general error: 2031 when uniting two queries (laravel datatables)

Question

Those are the queries that form the collection to be pushed to the laravel datatables builder:

foreach (Session::get('trienios') as $trienio) {
    $oeTrienios = $oeTrienios->where('data_trienio', $trienio->trienio)->whereHas('curso', function ($query) use ($trienio) {
        $query->where('curso', $trienio->curso);
    });
}

$union = Trienio::with('curso')->whereHas('curso', function ($query) use ($coordinatedCourse) {
    $query->where('curso', $coordinatedCourse);
})->union($oeTrienios);


$trienios = \DB::table(\DB::raw("({$union->toSql()}) as x"))->select(['data_trienio']);

There is a tutorial on the official laravel-datatables site that "explains" how to work with united queries, however it is pretty vague and can't realistically explain anything, and besides, when I tried to add a code they had on the tutorial:

$trienios = \DB::table(\DB::raw("({$union->toSql()}) as x"))

It gives me the following error:

SQLSTATE[HY000]: General error: 2031 (SQL: select count(*) as aggregate from (select `data_trienio` from ((select * from `trienios` where exists (select * from `cursos` where `trienios`.`curso_id` = `cursos`.`id` and `curso` = ?)) union (select * from `trienios` where `data_trienio` = ? and exists (select * from `cursos` where `trienios`.`curso_id` = `cursos`.`id` and `curso` = ?) and `data_trienio` = ? and exists (select * from `cursos` where `trienios`.`curso_id` = `cursos`.`id` and `curso` = ?))) as x) count_row_table)

If I, however, attach the parameter ->get() to the ->union($oeTrienios), it will work just fine, however, the collection will come unorderable on the datatable.

How do I solve this issue? Any help would be very welcome.

P.S - Link to the demo: https://datatables.yajrabox.com/fluent/union

Can you please mention link to the tutorial for uniting queries? — Ruchita Sheth
– Ruchita Sheth, Commented Jul 4, 2017 at 9:49

Andrii Lutskevych · Accepted Answer · 2017-07-04 09:53:35Z

3

The subquery $union->toSql() has only sql code without parameters and you need call bindings. See here and code will be:

$trienios = \DB::table(\DB::raw("({$union->toSql()}) as x"))
    ->select(['data_trienio'])
    ->mergeBindings($union);

answered Jul 4, 2017 at 9:53

Andrii Lutskevych

1,38913 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bad Laravel Programmer Over a year ago

throws this error: Argument 1 passed to Illuminate\Database\Query\Builder::mergeBindings() must be an instance of Illuminate\Database\Query\Builder, instance of Illuminate\Database\Eloquent\Builder given.

Einsamer Over a year ago

I think $union should be $union->getQuery(). Whatever, this one saved my life.

Collectives™ on Stack Overflow

general error: 2031 when uniting two queries (laravel datatables)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related