Beniamino_Baggins Beniamino_Baggins - 6 months ago 21
MySQL Question

Complex MySQL statement causing error

I am trying to combine two working sql statements into one working sql statement.

Here are the two sql statements that each work when done seperately:

This first one selects all products and the shops that the product is in, and the categories that the product has, for the shops that are in a specific area. It is for displaying the products on a website, then listing the shops and categories of the product under each product.

SELECT p.p_id, p.`p_name`, p.brand,
GROUP_CONCAT( DISTINCT CONCAT(c.c_id, ':', c.c_name) SEPARATOR ', ' ) as categories,
GROUP_CONCAT( DISTINCT CONCAT(s.s_id, ':', s.s_name) SEPARATOR ', ' ) as shops
FROM product p
INNER JOIN product_category pc on p.p_id = pc.p_id
INNER JOIN category c ON c.c_id = pc.c_id
INNER JOIN product_shop ps on p.p_id = ps.p_id
INNER JOIN shop s ON s.s_id = ps.s_id
WHERE s.street = 'college hill'
AND s.city = 'auckland'
AND s.province = 'auckland'
AND s.country = 'new zealand'
AND s.postalCode = '1011'


The results of my top sql statement look like this:

enter image description here

Important: I have since added latitude and longitude fields to the shop table which is important for the next sql statements in this question

This second sql statement is taken from this link that finds the closest 20 shops to a latlong coordinate in a table of shops. I have adapted it to work with my database:

SELECT s_id, ( 3959 * acos( cos( radians(-36.8473223
) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(174.744227
) ) + sin( radians(-36.8473223
) ) * sin( radians( latitude ) ) ) ) AS distance FROM shop HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;


It gives this result:

enter image description here

So by marrying these two sql statements, I can select the products that are in shops closest to a latlong coordinate, to display the products on the website, and then their shops and categories for each product.

All I am trying to do is add the distance of the shop from the given latitude and longitude coordinates (entered by the user with a google autocomplete place object), into the shop select results that I posted an image of for the top sql statement in this question. So in the shops results, for each shop just add a distance result which would be something like 5.897. And return the entries with the closest distance.

Here is my current attempt:

SELECT p.p_id,
p.`p_name`,
p.brand,
GROUP_CONCAT(DISTINCT CONCAT(c.c_id, ':', c.c_name) SEPARATOR ', '
) as categories,
GROUP_CONCAT(DISTINCT CONCAT(s.s_id, ':', s.s_name, ':',
( 3959 * acos( cos( radians(-36.8473223
) ) * cos( radians( s.latitude ) ) * cos( radians( s.longitude ) - radians(174.744227
) ) + sin( radians(-36.8473223
) ) * sin( radians( s.latitude ) ) ) ) AS distance) SEPARATOR ', '
) as shops
FROM
product p
INNER JOIN product_category pc on p.p_id = pc.p_id
INNER JOIN category c ON c.c_id = pc.c_id
INNER JOIN product_shop ps on p.p_id = ps.p_id
INNER JOIN shop s ON s.s_id = ps.s_id HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;


I am getting this error:


1583 - Incorrect parameters in the call to native function 'CONCAT'




What can I do to fix the error?

result from answer below:

enter image description here

Answer

You have an "As Distance" within your concat, then finishing the gorup concat as "Shops".

I have rewritten your query. The PREQUERY from shops computes your distance and has your limit of 20 entries... THEN it joins to the rest of the chain. Since "distance" is the computed column, that can now be used in your concat statement.

SELECT 
      p.p_id, 
      p.p_name,
      p.brand,
      GROUP_CONCAT( DISTINCT CONCAT(c.c_id, ':', c.c_name) 
         SEPARATOR ', ' ) as categories, 
      GROUP_CONCAT(DISTINCT CONCAT(s.s_id, ':', s.s_name, ':', 
         s.distance ) SEPARATOR ', ' ) as shops
   from
      ( SELECT 
              s_id, 
              s.s_name,
              ( 3959 
                * acos( cos( radians( -36.8473223 ) ) 
                * cos( radians( latitude ) ) 
                * cos( radians( longitude ) 
                - radians( 174.744227 ) ) 
                + sin( radians( -36.8473223 ) ) 
                * sin( radians( latitude ) ) ) ) AS distance 
           FROM 
              shop 
           HAVING 
              distance < 25 
           ORDER BY 
              distance 
           LIMIT 
              0, 20 ) s
      INNER JOIN product_shop ps 
         ON s.s_id = ps.s_id 
         INNER JOIN product p 
            on ps.p_id = p.p_id
            INNER JOIN product_category pc 
               on p.p_id = pc.p_id 
               INNER JOIN category c 
                   ON pc.c_id = c.c_id
   group by
      p.p_id,
      p.p_name,
      p.brand