Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

How to split a query?

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)

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.

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

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.



1.0k
Accepted
answer
edited 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();

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