October cms - Eloquent - Issues in creating model query from sql query

- 1 answer

Ad

I have a sql query that is working ok

SELECT 
*
FROM
    jml_gkb_eventos
WHERE
id IN (SELECT 
        evento_id
    FROM
        jml_gkb_etiqueta_evento
    WHERE
        etiqueta_id IN (SELECT 
                id
            FROM
                jml_gkb_etiquetas
            WHERE
                etiqueta REGEXP ? ) group by evento_id having count(evento_id) = ?);

But i can't figure out how convert this sql query to Eloquent model query. I know that i'm near solution (related with this problem here) and tried some variations of the following code:

        $pesquisa = preg_split('/\s+/', $temp, -1, PREG_SPLIT_NO_EMPTY);
    $cadeiapesquisa = implode('|', $pesquisa);
    $contagem = count($pesquisa);
    if (Session::get('modo') == 0){
        if ( strlen($cadeiapesquisa) > 0 ){
            $this['records'] = Evento::with('etiquetas')->whereHas('etiquetas', function($query) use ($cadeiapesquisa, $contagem){

                $query->where('etiqueta', 'regexp', "$cadeiapesquisa")->groupBy('evento_id')->having('COUNT(evento_id) = '.$contagem);

            })->orderBy('id', 'DESC')->paginate(25);
        } else {
            $this['records'] =Evento::paginate(25);
        }
    }

I get it working without the ->having() part in inner query, obviosly without the expected return, but without errors.

What i'm doing wrong ?

TIA

JL

[EDIT] - With the code above i get the following error:

Eloquent sql error

Ad

Answer

Ad

I found the problem. It is with the 'count()' part not being processed by eloquent. Placing the count on DB::raw is working as expected at least with the tests i've done. The whole snipet of code with some adjustements is:

        $pesquisa = preg_split('/\s+/', $temp, -1, PREG_SPLIT_NO_EMPTY);
    $cadeiapesquisa = implode('|', $pesquisa);
    $contagem = count($pesquisa);
    if (Session::get('modo') == 0){
        if ( strlen($cadeiapesquisa) > 0 ){
            $this['records'] = Evento::with('etiquetas')->whereHas('etiquetas', function($query) use ($cadeiapesquisa, $contagem){

                $query->where('etiqueta', 'regexp', "$cadeiapesquisa")->groupBy('evento_id')->having(DB::raw("COUNT('etiqueta_id')"), '>=', $contagem );

            })->paginate(25);
        } else {
            $this['records'] = Evento::paginate(25);
        }
    }

JL

Ad
source: stackoverflow.com
Ad