Hello! I can not understand how to make the right request. I want to make sorting by several types of tags. For example, in the request there are three tags: fantasy, action, comedy = $ catid I ​​want to get the following form through the request:

| 1 | Ρ„ΠΈΠ»ΡŒΠΌ 5 |фантастика,Π±ΠΎΠ΅Π²ΠΈΠΊ,комСдия| | 2 | Ρ„ΠΈΠ»ΡŒΠΌ 4 | фантастика,Π±ΠΎΠ΅Π²ΠΈΠΊ| | 3 | Ρ„ΠΈΠ»ΡŒΠΌ 8 | фантастика,комСдия| | 4 | Ρ„ΠΈΠ»ΡŒΠΌ 9 | Π±ΠΎΠ΅Π²ΠΈΠΊ,комСдия|

But with such a request

 $query = Film::groupBy('film.id')->with('categories'); $query = $query->join('category_film', 'film.id', '=', 'category_film.film_id') ->orderBy('name', 'asc') ->whereIn('category_film.category_id', $catid);

He is looking for where there is a mention of these tags. I get this look:

| 1 | Ρ„ΠΈΠ»ΡŒΠΌ 1 | фантастика| | 2 | Ρ„ΠΈΠ»ΡŒΠΌ 2 | комСдия| | 3 | Ρ„ΠΈΠ»ΡŒΠΌ 8 | Π±ΠΎΠ΅Π²ΠΈΠΊ| | 4 | Ρ„ΠΈΠ»ΡŒΠΌ 7 | фантастика| | 5 | Ρ„ΠΈΠ»ΡŒΠΌ 10| Π±ΠΎΠ΅Π²ΠΈΠΊ| | 6 | Ρ„ΠΈΠ»ΡŒΠΌ 9 | комСдия|

The tables themselves:

films contains:

| id | name |otherNames| slug| | 1 | Ρ„ΠΈΠ»ΡŒΠΌ 1| films 1|films_1| | 2 | Ρ„ΠΈΠ»ΡŒΠΌ 2| films 2|films_2| | 3 | Ѐильм 3| films 3|films_3| ... | id | name |otherNames| slug| | 1 | Ρ„ΠΈΠ»ΡŒΠΌ 1| films 1|films_1| | 2 | Ρ„ΠΈΠ»ΡŒΠΌ 2| films 2|films_2| | 3 | Ѐильм 3| films 3|films_3| ... category contains:

| id| name| slug| | 1 |фантастика | фантастика| | 2 | комСдия | комСдия| | 3 | Π±ΠΎΠ΅Π²ΠΈΠΊ | Π±ΠΎΠ΅Π²ΠΈΠΊ| ...

category_films linking table.

| films_id| category_id| | 5| 1| | 5| 2| | 5| 3| ...

I will be glad to solve this problem.

  • First, show how your movie tables, genres and their link table look like. - Vlad
  • I added the first message, I will be glad if you make a sql query for a mysql query. - Victor

1 answer 1

In your case, most likely the wrong connection is registered in the model, since, having checked, your code selected all the options from me, not just one of them.

In the Film model, the link should look like this:

 public function categories() { return $this->belongsToMany(Category::class, 'category_films'); }

Clean SQL Option:

 SELECT * FROM category_films cf JOIN films f ON f.id = cf.films_id JOIN category c ON cf.category_id = c.id WHERE p.id = <id>;

Lara style:

 DB::table('category_films') ->groupBy('films.id') ->Join('films', 'films.id', '=', 'category_films.films_id') ->Join('category', 'category.id', '=', 'category_films.category_id') ->get(); -------------МоТно Π΄ΠΎΠ±Π°Π²ΠΈΡ‚ΡŒ, Ссли Π½ΡƒΠΆΠ½ΠΎ ->Where() ->select() ->orderBy()
  • And how is the sorting from a larger match to a smaller one? - Victor
  • @ Victor And for this question it’s worth creating a new topic so that others in the future could also find the answer =) And if you received an answer to your question on this topic, please mark it with an answer) - Vlad
  • It seems that this was the essence of the question, and even an example showed how it should turn out, to search in several categories at once, from larger matches to smaller ones ... - Victor