Yii Framework Forum: Difficult query - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Difficult query Rate Topic: -----

#1 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 23 December 2009 - 08:57 AM

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
0

#2 User is offline   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 23 December 2009 - 09:34 AM

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

/Tommy
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#3 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 23 December 2009 - 09:58 AM

View Posttri, 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...
0

#4 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 23 December 2009 - 01:19 PM

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

0

#5 User is offline   bas_vdl 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 215
  • Joined: 01-April 09

Posted 23 December 2009 - 02:52 PM

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

0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users