My current config of tables is as follows
PagesTable
$this->belongsToMany('Keywords', ['through' => 'PagesKeywords']);
PagesKeywordsTable
Schema: id, page_id, keyword_id, relevance
$this->belongsTo('Pages');
$this->belongsTo('Keywords');
KeywordsTable
$this->belongsToMany('Pages', ['through' => 'PagesKeywords']);
Now heres what i'm trying to do..
Find pages via keywords, using an array to be precise then order by PagesKeywords.relevance
(This is basically storing how many time that keyword is repeated per page, so no duplicate keywords in join table)
I've currently got this working fine except it groups the results of keywords by the keyword itself, where as I need them to be grouped by Pages.id
Here is what i have in my Pages controller, search action:
$keywords = explode(" ", $this->request->query['q']);
$query = $this->Pages->Keywords->find()
->where(['keyword IN' => $keywords])
->contain(['Pages' => [
'queryBuilder' => function ($q) {
return $q->order([
'PagesKeywords.relevance' =>'DESC'
])->group(['Pages.id']);
}
]]);
$pages = array();
foreach($query as $result) {
$pages[] = $result;
}
I know this seems like a backward way to do things but its the only way I seemed to be able to order by _joinTable (PagesKeywords.relevance)
This returns the results I need but now it needs to be grouped by Pages.id which is where this whole thing goes to pot..
Just to be clear the structure I want is:
Page data 1
Page data 2
Page data 3
Page data 4
Where as its currently returning:
Keyword "google"
------- Page data 1
------- Page data 2
------- Page data 3
------- Page data 4
Keyword "something"
------- Page data 1
------- Page data 2
------- Page data 3
------- Page data 4
If you are able to help me thats great!
Thanks