Hello, i need some advice/tips for getting the right data out of my database.
The users need to search for daytrips based on categories. The site will print the daytrips including the Categories and Tags.
So i first wrote the query for MySql. But when a Daytrip belongs to cat1, cat2, cat3 cat4 the group_concat will return cat1, cat2 instead of cat1, cat2, cat3 cat4.
so i need al the categories of a daytrip not the one that is in de where condition.
question… how to build this in yii? can i do this with 1 query do i need multiple etc etc.
thank you very much!
SELECT d.IdDaytrip, d.Daytrip, GROUP_CONCAT(t.Tag SEPARATOR ', '), GROUP_CONCAT(ca.Category SEPARATOR ', '), geodistkm(52.1916681559, 5.96557617188, c.Latitude, c.Longitude) AS Distance
FROM Daytrips d
INNER JOIN Cities c ON c.IdCity = d.CityId
INNER JOIN DaytripTag dt ON dt.DaytripId = d.IdDaytrip
INNER JOIN Tags t ON t.IdTag = dt.TagId
INNER JOIN DaytripCategory dc ON dc.DaytripId = d.IdDaytrip
INNER JOIN Categories ca ON ca.IdCategory = dc.CategoryId
WHERE ca.IdCategory IN (1,2)
GROUP BY d.IdDaytrip, d.Daytrip
ORDER BY Distance
LIMIT 100
TABLES:
Daytrips
IdDaytrip
Daytrip
CityId
Cities
IdCity
City
Latitude
Longitude
DaytripTag
DaytripId
TagId
Tags
IdTag
Tags
DaytripCategory
DaytripId
CategoryId
Categories
IdCategory
Category