mercredi 2 janvier 2019

SQL Syntax Error from Laravel Query Builder

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