How to split a query?

Question

How to split a query?

Pagana0709 Mar '20

Created Mar '20	Last Reply Mar '20	Replies 3	Views 173	Votes 0

Pagana0709 1.8k

Mar '20

I have the following code:

- ex1:

$ciclo_id = 0 // value sent from the op

// queries
if ($ciclo_id == 0) {
    $get_totales = $this->modelsManager->createBuilder()
        ->columns([
            'gru.nombre',
            'sum(!isnull(usu.anio_ingreso)) as ingresos',
            'sum(!isnull(usu.anio_egreso)) as egresos',
            'sum(!isnull(usu.anio_baja)) as bajas'
        ])            
        ->from(['usu' => Usuarios::class])
        ->rightJoin(Grupo::class, 'gru.grupo_id = usu.grupo', 'gru')
        ->groupBy('gru.grupo_id')
        ->orderBy('gru.grupo_id asc')
        ->getQuery()
        ->execute();
} else {
    $get_totales = $this->modelsManager->createBuilder()
        ->columns([
            'gru.nombre',
            'sum(!isnull(usu.anio_ingreso)) as ingresos',
            'sum(!isnull(usu.anio_egreso)) as egresos',
            'sum(!isnull(usu.anio_baja)) as bajas'
        ])            
        ->from(['usu' => Usuarios::class])
        ->rightJoin(Grupo::class, 'gru.grupo_id = usu.grupo', 'gru')
        ->where('usu.ciclo = :ciclo:', ['ciclo' => $ciclo_id])
        ->groupBy('gru.grupo_id')
        ->orderBy('gru.grupo_id asc')
        ->getQuery()
        ->execute();
}

The query serves to bring data according to the option chosen by a user.

I want simplify the previous code something like:

- ex2:

$get_totales = $this->modelsManager->createBuilder()
    ->columns([
        'gru.nombre',
        'sum(!isnull(usu.anio_ingreso)) as ingresos',
        'sum(!isnull(usu.anio_egreso)) as egresos',
        'sum(!isnull(usu.anio_baja)) as bajas'
    ])            
    ->from(['usu' => Usuarios::class])
    ->rightJoin(Grupo::class, 'gru.grupo_id = usu.grupo', 'gru');

    if ($ciclo_id > 0) {
        $get_totales->where('usu.ciclo = :ciclo:', ['ciclo' => $ciclo_id]);
    }

    $get_totales->groupBy('gru.grupo_id')
    ->orderBy('gru.grupo_id asc')
    ->getQuery()
    ->execute();

My problem is when I use the first example I can get results and with second example I get nothing regardless of the value of the variable $ciclo_id

Any ideas? Thanks.

Extra info:

When I dump $get_totales in example 1 returns object(Phalcon\Mvc\Model\Resultset\Simple), the example 2 gives object(Phalcon\Mvc\Model\Query\Builder)

Alistar84
1.1k

Accepted
answer

edited Mar '20
Mar '20

Try this way

$get_totales = $this->modelsManager->createBuilder()
    ->columns([
        'gru.nombre',
        'sum(!isnull(usu.anio_ingreso)) as ingresos',
        'sum(!isnull(usu.anio_egreso)) as egresos',
        'sum(!isnull(usu.anio_baja)) as bajas'
    ])            
    ->from(['usu' => Usuarios::class])
    ->rightJoin(Grupo::class, 'gru.grupo_id = usu.grupo', 'gru');

if ($ciclo_id > 0) {
    $get_totales = $get_totales->where('usu.ciclo = :ciclo:', ['ciclo' => $ciclo_id]);
}

$get_totales = $get_totales->groupBy('gru.grupo_id')
    ->orderBy('gru.grupo_id asc')
    ->getQuery()
    ->execute();

Dylan Anderson · Answer 1 · 2020-03-17T12:30:23-07:00

That looks very close. But, only add the where clause if $ciclo_id != 0 - that's what the code shows in your first example.

In your second example, you are adding the where clause twice. First inside your if condition, and then again below that condition. You can remove the second where clause.

Pagana0709 · Answer 2 · 2020-03-17T17:14:14-07:00

You're right. I made a mistake in posting the code in the second example. I updated it.

The problem is when I use the first example I can get results and with second example I get an empty result regardless of the value of the variable $ciclo_id

Dylan Anderson · Answer 3 · 2020-03-18T08:00:39-07:00

That sucks - it looks like it's exactly the same.

If I were in your position, I'd turn on server-side query logging, or add deep sql-level logging ( https://stackoverflow.com/a/24886819/251859 ) and see what the generated query is.

You almost certainly have mis-typed something, but I can't see what.

Pagana0709 · Answer 4 · 2020-03-25T13:12:25-07:00

Pagana0709
1.8k

Mar '20

That's what I was looking for. Thank you.