2

Here is a little bit of information about the problem - I am currently using google maps to create a small application where people have a profile page which contains a Google map. They can drop markers on that map and stuff like city, country, latitude, and longitude of these markers gets saved in the database.

Now I want to create a list of cities which have been marked, however, if a User creates 2 marks in a certain city, the name of that city gets displayed twice which is unwanted behavior. This is why I am wondering whether I can exclude the duplicate record from my collection, for example, if I have this:

Database records:

id    city    country    lat    lng
1     London  UK         1      1
2     London  UK         1      1
3     Tokyo   Japan      2      2

Is it possible to only return 2 records based on the city, the first one and the third one while excluding the second one because it's duplicate?

I'm currently getting the cities like this:

$cities = Marker::where('user_id', $userId)->get();

Table: Markers
Columns: id, city, country, place_id, user_id, lat, lng, created_at, updated_at
Onyx
  • 5,186
  • 8
  • 39
  • 86
  • try to group by city name. $cities = Marker::where('user_id', $userId)->groupBy('city')->get(); – shubham singh Jan 21 '19 at 09:36
  • you should modify the table structure it seems – Vickrant Jan 21 '19 at 09:39
  • Can you make (city,country) unique??? – Vickrant Jan 21 '19 at 09:39
  • I still want the user to be able to place more than 1 mark in a certain city. Wouldn't making city and country unique make that impossible? – Onyx Jan 21 '19 at 09:40
  • Yeah in that case it will not register further records. Agreed. – Vickrant Jan 21 '19 at 09:41
  • Have you tried as suggested by @shubhamsingh – Vickrant Jan 21 '19 at 09:42
  • @shubhamsingh I get an error when I try your solution. Do you happen to know why? "Syntax error or access violation: 1055 'spa_api.markers.id' isn't in GROUP BY (SQL: select * from `markers` where `user_id` = 1 group by `city`)" – Onyx Jan 21 '19 at 09:44
  • group by your column name which contains city name. – shubham singh Jan 21 '19 at 09:46
  • if able to show your table structure.than i will able to give u exact answer – shubham singh Jan 21 '19 at 09:47
  • Well the column that contains the city is indeed called city :D. I'll update my question with database table. – Onyx Jan 21 '19 at 09:50
  • Might want to group by some kind of compound of city and country - otherwise there could be a lot of confusion between the UK and North America ... e.g. *Boston (Mass.)* and *Boston (Lincs.)* or *Washington (DC)* and *Washington (Tyne & Wear)* ... and I I believe there's at least one London in Canada :) – CD001 Jan 21 '19 at 09:53
  • That is a very good and valid suggestion CD001 – Onyx Jan 21 '19 at 09:56

1 Answers1

2

Can you try group by.

$cities = Marker::where('user_id', $userId)->groupBy('city')->get();

Kiran Kanzar
  • 329
  • 1
  • 5
  • 14