Hi all

I need some help with this 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

Last updated 7 years ago

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 {
       $this['records'] = Evento::paginate(25);
    }
}
}

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

TIA

JL

EDIT: I fount a typo on last statement. Corrected it from

 Evento::paginate(25);

to

 $this['records'] = Evento::paginate(25);

Last updated 7 years ago

Finally after some time digging and trying to understand what was wrong i found the solution to all problem. The resulting snipet of code that is working so far with some 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