Difficult query

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

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…

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



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