OctoberCMS - Model query with relation manyToMany

Question

Once again i'm asking some help from the community... Scenario:

I have two tables with one pivot table (something like posts and tags to give some context):

    table 1 (Evento):
        -id
        -....

    table 2 (Etiquetas):
        -id
        -etiqueta

    pivot table (Eventos_Etiquetas):
        -evento_id (pk)
        -etiqueta_id (pk)

The relations are set as:

public $belongsToMany = [
    'eventos' => ['JML\Gkb\Models\Evento', 'table' => 'jml_gkb_eventos_etiquetas']
];

and

public $belongsToMany = [
    'etiquetas' => ['JML\Gkb\Models\Etiqueta', 'table' => 'jml_gkb_eventos_etiquetas']
];

Now, what i want to achieve:

-get all events that have any individual tag without regarding the order of input (or).

-get all events that have all tags without regarding the order of input (and).

As you can imagine i'm strugling with this as i'm new to October/Laravel query builder (and not so to sql).

What i've done so far:

if (Session::get('tipo') == 'etiqueta'){
    $pesquisa = preg_split('/\s+/', $temp, -1, PREG_SPLIT_NO_EMPTY);
    if (Session::get('modo') == 0){
        $this['records'] = Evento::with('etiquetas')->whereHas('etiquetas', function($query) use ($pesquisa){
            foreach ($pesquisa as $palavra){
                $query->where('etiqueta', 'like', "%$palavra%");
            }
        })->orderBy('id', 'DESC')->paginate(25);
    }
    if (Session::get('modo') == 1){
        $this['records'] = Evento::with('etiquetas')->whereHas('etiquetas', function($query) use ($pesquisa){
            foreach ($pesquisa as $palavra){
                $query->orWhere('etiqueta', 'like', "%$palavra%");
            }
        })->orderBy('id', 'DESC')->paginate(25);
    }
}

The user input is passed by $temp variable and it's splited to words to the $pesquisa array variable. 'modo' defines if the user pretend a search by AND (0) or by OR (1). Based on that choice a query is built to try to get the results using $palavra variable as any word of $pesquisa.

The result of this:

In modo == 0, i only can get the events of one tag of user input, if it have more than one word (any letter that don't exist on first word) don't get any result.

In modo == 1 it gets all events.

In both cases i don't get any event that don't have any tag (etiqueta) - correct behaviour.

I've tried some other ways but with no avail... This one looks to me the most logical of the tries... Can someone point me on the correct direction ?

TIA

JL

Answer 1

After some tries i have half of the problem solved. The part where i want to get any "Evento" that has any of the "Etiqueta" on user input not regarding the order of them is working fine finally.

Resume bellow

    if (Session::get('tipo') == 'etiqueta'){
    $pesquisa = preg_split('/\s+/', $temp, -1, PREG_SPLIT_NO_EMPTY);
    $cadeiapesquisa = implode('|', $pesquisa);
 /***** NOT WORKING YET *****/
    if (Session::get('modo') == 0){
        $this['records'] = Evento::with('etiquetas')->whereHas('etiquetas', function($query) use ($pesquisa){
            foreach ($pesquisa as $palavra){
                $query->where('etiqueta', 'like', "%$palavra%");
            }
        })->orderBy('id', 'DESC')->paginate(25);
    }
/****** THIS IS WORKING FINE ! *******/
    if (Session::get('modo') == 1){
        if ( count ($pesquisa) > 0 && !($temp == null)){
            $this['records'] = Evento::with('etiquetas')->whereHas('etiquetas', function($query) use ($cadeiapesquisa){

                $query->where('etiqueta', 'regexp', "$cadeiapesquisa");

            })->orderBy('id', 'DESC')->paginate(25);
        } else {
            Evento::paginate(25);
        }
    }
}

The first parte is fighting, but i will get there. :)

TIA

JL

[EDIT]

Problem solved... The resulting snipet of code working so far with the tests i've done is bellow:

    if (Session::get('tipo') == 'etiqueta'){
    $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);
        }
    }
    if (Session::get('modo') == 1){
        if ( strlen($cadeiapesquisa) > 0 ){
            $this['records'] = Evento::with('etiquetas')->whereHas('etiquetas', function($query) use ($cadeiapesquisa){

                $query->where('etiqueta', 'regexp', "$cadeiapesquisa");

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

JL