[bas_vdl]

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

[tri]

Is it that DaytripCategory needs to be outer joined in order to display all daytrips belonging to the category set given?

/Tommy

[bas_vdl]

tri, on 23 December 2009 - 09:34 AM, said:

Is it that DaytripCategory needs to be outer joined in order to display all daytrips belonging to the category set given?

/Tommy

SELECT d.IdDaytrip, d.Daytrip, GROUP_CONCAT(DISTINCT t.Tag SEPARATOR ', '), GROUP_CONCAT(DISTINCT 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
LEFT OUTER 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

LEFT OUTER JOIN DaytripCategory dc ON dc.DaytripId = d.IdDaytrip

will still print only the two selected categories (1,2) instead of all...

[bas_vdl]

found it, with a second join it is working.

now i have to translate this to yii. i think i'll need some help with that

SELECT d.IdDaytrip, d.Daytrip, GROUP_CONCAT(DISTINCT t.Tag SEPARATOR ', '), GROUP_CONCAT(DISTINCT ca.Category SEPARATOR ', '), GROUP_CONCAT(DISTINCT ca2.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
INNER JOIN DaytripCategory dc2 ON dc2.DaytripId = d.IdDaytrip
INNER JOIN Categories ca2 ON ca2.IdCategory = dc2.CategoryId
WHERE ca2.IdCategory IN (6,1)
GROUP BY d.IdDaytrip, d.Daytrip
ORDER BY Distance
LIMIT 100

[bas_vdl]

almost there...

with the relation like below it will print sometimes a couple of categories that doesn't belong to that Daytrip

    public function relations() {
        return array(
            'rCity' => array(self::BELONGS_TO, 'City', 'CityId'),
            'rCategory' => array(self::MANY_MANY, 'Category', 'DaytripCategory(DaytripId, CategoryId)',
                'select' => array('GROUP_CONCAT(DISTINCT rCategory.Category SEPARATOR ", ") AS Categories'),
                'group' => 'Daytrips.IdDaytrip'
            ),
            'categoryFilter' => array(self::MANY_MANY, 'Category', 'DaytripCategory(DaytripId, CategoryId)',
                'condition' => 'categoryFilter.IdCategory IN ' . new CDbExpression('(:IdCategory)')
            ),
            'rTag' => array(self::MANY_MANY, 'Tag', 'DaytripTag(DaytripId, TagId)',
                'select' => array('GROUP_CONCAT(DISTINCT rTag.Tag SEPARATOR ", ") AS Tags'),
                'group' => 'Daytrips.IdDaytrip'
            ),
        );
    }

will generate:

SELECT
	`Daytrips`.`Daytrip` AS `t0_c1`,
	`Daytrips`.`YouTube` AS `t0_c11`,
	geodistkm(52.0568469054, 5.17456054688, rCity.Latitude, rCity.Longitude) AS Distance,
	`Daytrips`.`IdDaytrip` AS `t0_c0`,
	`rCity`.`IdCity` AS `t1_c0`,
	`rCity`.`CountryId` AS `t1_c1`,
	`rCity`.`Province` AS `t1_c2`,
	`rCity`.`Municipality` AS `t1_c3`,
	`rCity`.`City` AS `t1_c4`,
	`rCity`.`Latitude` AS `t1_c5`,
	`rCity`.`Longitude` AS `t1_c6`,
	`rCity`.`GoogleMatch` AS `t1_c7`,
	GROUP_CONCAT(DISTINCT rCategory.Category SEPARATOR ", ") AS Categories,
	`rCategory`.`IdCategory` AS `t2_c0`,
	GROUP_CONCAT(DISTINCT rTag.Tag SEPARATOR ", ") AS Tags,
	`rTag`.`IdTag` AS `t3_c0`,
	`categoryFilter`.`IdCategory` AS `t4_c0`,
	`categoryFilter`.`Category` AS `t4_c1`
FROM `Daytrips`
LEFT OUTER JOIN `Cities` `rCity` ON (`Daytrips`.`CityId`=`rCity`.`IdCity`) 
LEFT OUTER JOIN `DaytripCategory` `rCategory_rCategory` ON (`Daytrips`.`IdDaytrip`=`rCategory_rCategory`.`DaytripId`) 
LEFT OUTER JOIN `Categories` `rCategory` ON (`rCategory`.`IdCategory`=`rCategory_rCategory`.`CategoryId`) 
LEFT OUTER JOIN `DaytripTag` `rTag_rTag` ON (`Daytrips`.`IdDaytrip`=`rTag_rTag`.`DaytripId`) 
LEFT OUTER JOIN `Tags` `rTag` ON (`rTag`.`IdTag`=`rTag_rTag`.`TagId`) 
LEFT OUTER JOIN `DaytripCategory` `categoryFilter_categoryFilter` ON (`Daytrips`.`IdDaytrip`=`categoryFilter_categoryFilter`.`DaytripId`) 
LEFT OUTER JOIN `Categories` `categoryFilter` ON (`categoryFilter`.`IdCategory`=`categoryFilter_categoryFilter`.`CategoryId`)
WHERE (categoryFilter.IdCategory IN (:IdCategory))
GROUP BY Daytrips.IdDaytrip, Daytrips.IdDaytrip
ORDER BY Distance ASC, IdDaytrip
LIMIT 100