Basically I am trying to get related articles of an article by tags
following the solution of this : https://stackoverflow.com/a/30885226/7360506
So, I have constructed a query using query builder to achieve the solution like SQL Query
My Query Builder Solution :
$sub1 = DB::table(config('db.tables.BlogArticleTags').' AS at')
->select('at.article_id', 'at.tag_id')
->where('at.article_id', $article_id_obj->article_id);
$sub2 = DB::table(config('db.tables.BlogArticleTags').' AS at')
->select('at.article_id', 'at.tag_id')
->where('at.article_id', '!=', $article_id_obj->article_id);
$query = DB::table( DB::raw("({$sub1->toSql()}) as t1") )
//->mergeBindings($sub1->getQuery())
->joinSub($sub2, 't2', function($join) {
$join->on('t1.tag_id', '=', 't2.tag_id');
})
->select('t2.article_id', DB::raw('COUNT(t2.tag_id) AS matches'))
->groupBy('t2.article_id')
->orderBy('matches', 'DESC')
//->get();
->toSql();
So, In $query
I am getting this with an MySqlException
:
"select `t2`.`article_id`, COUNT(t2.tag_id) AS matches from (select `at`.`article_id`, `at`.`tag_id` from `blog_article_tags` as `at` where `at`.`article_id` = ?) as t1 inner join (select `at`.`article_id`, `at`.`tag_id` from `blog_article_tags` as `at` where `at`.`article_id` != ?) as `t2` on `t1`.`tag_id` = `t2`.`tag_id` group by `t2`.`article_id` order by `matches` desc"
Exception : SQLSTATE[HY093]: Invalid parameter number
I am guessing the problem is in the sub2
when it is trying to execute : where at.article_id != ?
Here I am not getting 9
: article_id
Where I am doing wrong in my query builder solution ?
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire