0

Am trying to select all items from item_table based on user distance from the seller stores. Below code actually work fine till i use FIND_IN_SET to allow showing one product in two or more different location if the location loc_id is in array of item item_loc_ids.

What i actually want to archive is to show one product to users from different locations provided the store coordinate is within the user location and the location ID is in list of locations where the item should be available based on distance calculation.

seller_table

seller_name      | seller_id   
-----------------|-----------------
 Peter           | PP1
 John            | JJ6

location_table 

 loc_name  | loc_id   |  loc_lat  | loc_lng     | seller_id
-----------|----------|-----------|-------------|----------
 Office 1  | 1A       | 2.908810  | 101.651398  | PP1
 Office 2  | 1B       | 3.051953  | 101.671417  | PP1
 Outlet S  | 3A       | 3.051953  | 101.671455  | JJ6

Item_table 

 item_name  | item_id  | item_loc_ids   | item_seller
 -----------|----------|----------------|------------------
 Java       | 1        | 1A, 1B         | PP1
 PHP        | 2        | 1A             | PP1
 CSS        | 3        | 1B             | PP1
 SQL        | 4        | 1B, 1A         | PP1
 Python     | 5        | 3A             | JJ6

User Data

 $userLatitude = 2.908810;
 $userLongitude = 101.651398;
 $userDistance  = 5.5;

Query

SELECT seller.*,loc.*, item.*, 3956 * 2 * ASIN(SQRT(POWER(SIN((loc.loc_lat - ABS($userLatitude)) * PI()/180 / 2), 2) + COS(loc.loc_lng * PI()/180 ) * COS(ABS( $userLatitude) * PI()/180)  * POWER(SIN((loc.loc_lng - $userLongitude) * PI()/180 / 2), 2))) AS distance
FROM (
    SELECT item.*,
    ROW_NUMBER() OVER(PARTITION BY Item.item_seller ORDER BY Item.item_seller) AS limit_group
    FROM Item_table Item
) p
INNER JOIN seller_name seller
ON Item.item_seller = seller.seller_id

INNER JOIN location_table loc
ON FIND_IN_SET(loc.loc_id, Item.item_loc_ids)

WHERE limit_group <= 5
    HAVING distance <=  $userDistance 
    OR distance IS NULL
    ORDER BY distance IS NULL DESC, distance DESC
LIMIT 100

The above code work only on one location_id, but if more it won't return any record.

I added a link to sql fiddle http://sqlfiddle.com/#!9/8c085b/4

and DDLs of same:

 CREATE TABLE `location_table` (
    `loc_name` VARCHAR(50) NULL DEFAULT NULL,
    `loc_id` VARCHAR(50) NULL DEFAULT NULL,
    `loc_lat` VARCHAR(500) NULL DEFAULT NULL,
    `loc_lng` VARCHAR(500) NULL DEFAULT NULL,
    `seller_id` VARCHAR(500) NULL DEFAULT NULL
);

 INSERT INTO `location_table` (`loc_name`, `loc_id`, `loc_lat`, `loc_lng`, `seller_id`) 
 VALUES ('Office 1', '1A', '2.908810', '101.651398', 'PP1'),  
 ('Office 1', '1B', '3.051953', '101.671417', 'PP1'),
 ('Outlet S', '3A', '3.051953', '101.671455', 'JJ6');


CREATE TABLE `seller_table` (
    `seller_name` VARCHAR(50) NULL DEFAULT NULL,
    `seller_id` VARCHAR(50) NULL DEFAULT NULL
);

INSERT INTO `seller_table` (`seller_name`, `seller_id`) VALUES ('Peter', 'PP1'),  ('John', 'JJ6');


 CREATE TABLE `item_table` (
    `item_name` VARCHAR(50) NULL DEFAULT NULL,
    `item_id` VARCHAR(50) NULL DEFAULT NULL,
    `item_store_ids` VARCHAR(100) NULL DEFAULT NULL,
    `item_seller` VARCHAR(50) NULL DEFAULT NULL
);

  INSERT INTO `item_table` (`item_name`, `item_id`, `item_store_ids`, `item_seller`) VALUES ('Java', '1', '1A, 1B ', 'PP1'), 
  ('PHP', '2', '1A ', 'PP1'), 
  ('CSS', '3', '1B ', 'PP1'), 
  ('SQL', '4', '1B, 1A ', 'PP1'),
  ('Python', '5', '3A ', 'JJ6');

  SELECT seller.*,loc.*, item.*, 3956 * 2 * ASIN(SQRT(POWER(SIN((loc.loc_lat - ABS(2.908810)) * PI()/180 / 2), 2) + COS(loc.loc_lng * PI()/180 ) * COS(ABS(2.908810) * PI()/180)  * POWER(SIN((loc.loc_lng - 101.651398) * PI()/180 / 2), 2))) AS distance
FROM (
    SELECT item.*,
    ROW_NUMBER() OVER(PARTITION BY item.item_seller ORDER BY item.item_seller) AS limit_group
    FROM item_table item
) p
INNER JOIN seller_table seller
ON item.item_seller = seller.seller_id

INNER JOIN location_table loc
ON FIND_IN_SET(loc.loc_id, item.item_store_ids)

WHERE limit_group <= 5
    HAVING distance <=  5.5 
    OR distance IS NULL
    ORDER BY distance IS NULL DESC, distance DESC
LIMIT 100
Strawberry
  • 33,750
  • 13
  • 40
  • 57
Peter
  • 1,860
  • 2
  • 18
  • 47
  • Please send table create code and insert statements. Finally expected output format. – Dark Knight Aug 27 '19 at 18:00
  • Since you are using `ROW_NUMBER()` you have at least MySQL 8.0 - So why don't you use `ST_Distance()` or `ST_Distance_Sphere()`? – Paul Spiegel Aug 27 '19 at 18:07
  • @JitendraYadav okay i will update my question with a link to table – Peter Aug 27 '19 at 18:11
  • @PaulSpiegel i will love to if you can give me a sample query, before i could get query working it too 6 days before my question was answered – Peter Aug 27 '19 at 18:12
  • See: [ST_Distance_Sphere()](https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-distance-sphere) and [this fiddle](https://www.db-fiddle.com/f/R1iF5Ltna2pWYYGrTAenc/0) – Paul Spiegel Aug 27 '19 at 18:20
  • 1
    Also see: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Paul Spiegel Aug 27 '19 at 18:23
  • @JitendraYadav I have updated my question – Peter Aug 27 '19 at 18:46
  • @PaulSpiegel please i don't think my question is same with the answered reference which was marked. – Peter Aug 27 '19 at 19:03
  • @Peter, the suggested design from Paul is good. But I think for your scenario, you can just update your on clause by this `FIND_IN_SET(loc.loc_id, REPLACE(Item.item_store_ids, ' ', ''))` – Dark Knight Aug 27 '19 at 20:22
  • @Peter I didn't close the question. I only posted the link. – Paul Spiegel Aug 28 '19 at 15:55
  • See additional duplicate references. You need to remove the spaces after the comma. If that doesn't help, then it's unclear what you are asking. – Paul Spiegel Aug 28 '19 at 16:12

0 Answers0