Hi, I have a search query where I’m looking for all People where they have the chosen UserAttributes. Each UserAttribute is part of a Group, when searching the the relationship between the UserAttribute Groups is AND and the Attribute value is OR. An example would be, you search for People in the Attribute Group “sex”, selecting “male” OR “female” AND in another Attribute Group “hair colour”, selecting “blonde” OR “brown” OR “black”.
The following query works for selecting People that have selected UserAttributes that are in the UserAttributes Groups.
SELECT DISTINCT `Person`.`ID`, `Person`.`Firstname`, `Person`.`Surname`, COUNT(*), GROUP_CONCAT(CAST(`UserAttribute`.`UserAttributeGroupID` AS char)) as uagc
FROM Person, Person_UserAttributes, UserAttribute
WHERE (`Person`.`ID` = `Person_UserAttributes`.`PersonID`
AND `UserAttribute`.`ID` = `Person_UserAttributes`.`UserAttributeID`
AND `Person_UserAttributes`.`UserAttributeID` IN (41,42,43,11,12,51,75,15,16,17,9,10)
)
GROUP BY `Person`.`ID` HAVING ( COUNT(`UserAttribute`.`UserAttributeGroupID`) = 4 )
The UserAttribute Group count is 4, that number is from the code because you can tell from the POST Array (PHP) that the group number is 4. This will of course change with the amount of different UserAttributes selected from different UserAttribute Groups. I can see from the result the count is 4, the GROUP_CONCAT shows the UserAttribute Group ids.
The problem is when I want to search not only for the User Attributes but for other criteria such as Hobbies. The following shows no results.
SELECT DISTINCT `Person`.`ID`, `Person`.`Firstname`, `Person`.`Surname`, COUNT(*), GROUP_CONCAT(CAST(`UserAttribute`.`UserAttributeGroupID` AS char)) as uagc
FROM Person, Person_UserAttributes, UserAttribute, Hobbies
WHERE (`Person`.`ID` = `Person_UserAttributes`.`PersonID`
AND `UserAttribute`.`ID` = `Person_UserAttributes`.`UserAttributeID`
AND `Person_UserAttributes`.`UserAttributeID` IN (41,42,43,11,12,51,75,15,16,17,9,10)
AND `Person`.`ID` = `Hobbies`.`PersonID` AND `Hobbies`.`HobbyCategoryID` IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118)
)
GROUP BY `Person`.`ID` HAVING ( COUNT(`UserAttribute`.`UserAttributeGroupID`) = 4 )
I can only get results from the new query if I change COUNT(UserAttribute
.UserAttributeGroupID
) = 4 to this COUNT(UserAttribute
.UserAttributeGroupID
) >= 4, and the count takes into account the Hobby Categories.
I know my approach must be wrong but I have no idea what to do next :(, please help