Many-to-Many relationship problem (ALL subquery?)
Basic layout
Resource
IdResource
Name
Keyword
IdKeyword
Name
There is a many-to-many association table connecting the two.
Problem:
The keywords are used as filters. A user can select any number of keywords and it should filter the results such that each resource has ALL of the keywords selected associated (and any others not selected - the important point is that all that are selected are associated).
I can implement in PHP manipulating arrays ... but that's *really* slow when dealing with thousands of resources.
I essentially want to do:
QQ::In_All(QQN::Resource()->Keyword->Keyword->IdKeyword, $arrSelectedKeywords) ... something like an SQL subquery with the ALL operator such that it will “return TRUE if the comparison is TRUE for ALL of the values in the column that the subquery returns.”
Any ideas? It's probably really simple - I'm just a bit perplexed.
Thanks,
Andrew
Well, currently there's no way to do this. But! We've got it built and ready to go:
1) Apply the patch in http://trac.qcu.be/projects/qcubed/ticket/321
2)
$conditions = QQ::All();foreach($keywordArr as $keywordId)
{
$conditions = QQ::AndCondition($conditions,
QQ::Equal(QQ::Alias(QQN::Resource()->Keywords, 'k'.$keywordId)->KeywordId, $keywordId)
);
}
$resources = Resource::QueryArray($conditions);
With the posted solution, I get SQL similar to what is found below. I, unfortunately, don't get any records returned...
I feel this is probably due to the fact that `t1`.`Keyword_idKeyword` can't be both 50 and 7 at the same time. The left-join is certainly down the right path ... getting a single set of resources from 't1' is the problem.
SELECTCOUNT(*) AS q_row_count
FROM
`Resource` AS `t0`
LEFT JOIN `Resource_Keyword_assn` AS `t1` ON `t0`.`idResource` = `t1`.`Resource_idResource`
WHERE(
(1=1 AND `t1`.`Keyword_idKeyword` = 50)
AND
`t1`.`Keyword_idKeyword` = 7)
This is what the alias is intended to prevent from happening. It shouldn't be joining Resource_Keyword_assn as `t1`, it should be joining it once as k50 and once as k7.
Are you sure you're using the code I supplied?
Yessir (using the 2nd version of your patch, too) - the patch only modifies QQuery.class.php, correct?:
$conditions = QQ::All();
foreach($arrSelectedKeywords as $keyword)
{
$conditions =
QQ::AndCondition(
$conditions,
QQ::Equal(QQ::Alias(QQN::Resource()->Keyword, 'k'.$keyword->IdKeyword)->KeywordIdKeyword, $keyword->IdKeyword)
);
}
$this->dtrResource->DataSource = Resource::QueryArray(
$conditions,
QQ::Clause(
$this->dtrResource->LimitClause
)
);
$this->dtrResource->TotalItemCount =
Resource::QueryCount(
$conditions
);
Huh. That sure looks right to me. But there should be no way for that to produce SQL without a table aliased as k-something. (You're effectively overriding the t1 alias with a k-something one.)
Have you tried this on the example database, to ensure the example page provided in that ticket works as expected?
It's behaving like you didn't change all the strName to strAlias in QQuery, or that you never set strAlias, which you clearly did.
Yes ... my mistake. Two strName entries weren't changed and now it works. I should've double checked.
Thanks a million!
-Andrew
Glad it works! I think you're the first other person to test that for me. :) Hopefully it'll be commited to core soon. :)