Monday, March 20, 2017

OctoberCMS - Model query with relation manyToMany

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



via JLongo

Advertisement