Ad

Retrieve Entries From Table Via Pivot Table Octobercms

- 1 answer

can anyone help me writing query to retrieve words from my words table in such way that words are having a belongsToMany relationship to Type model via types pivot table?

Here's how relationship looks like in Word.php

 public $belongsToMany = [
    'typeswb' => [
        'Yeoman\Wordbank\Models\Type',
        'table' => 'yeoman_wordbank_types_pivotb',
        'order' => 'typesofwordbank'
    ]
];

Here is how types table looks like

    mysql> select * from yeoman_wordbank_types;
+----+--------------------+--------------------+
| id | typesofwordbank    | slugoftypes        |
+----+--------------------+--------------------+
|  1 | Common Words       | common-words       |
|  2 | Common Expressions | common-expressions |
|  3 | Common Proverbs    | common-proverbs    |
|  4 | Common Idioms      | common-idioms      |
+----+--------------------+--------------------+
4 rows in set (0.00 sec)

and wheres how types pivot table looks like

mysql> select * from yeoman_wordbank_types_pivotb;
+---------+---------+
| word_id | type_id |
+---------+---------+
|      18 |       2 |
|       5 |       4 |
|       9 |       3 |
|      19 |       1 |
|      31 |       1 |
+---------+---------+
5 rows in set (0.00 sec)

As you can see type_id are connected to words_id. where types_id's are from types table and words_id's are from word table.

I need to find a way to get the words using types_id.

I have tried following

// $query = Word::all();
// foreach ($query->typeswb as $typeswb) {
   // $queryy = $typeswb->pivot->type_id = 1;
// }

and some other combination like that but all in vain, strangely I get Word::find(1) null on this while Word::all() return an array of 6 items in the collection.

Thank you for reading, I will appreciate any hint or help very much.

Ad

Answer

ok so i solved this, normally via builder component, this functionality is very easy to achieve but was kinda of unclear how to do on my own in one of my own component. So, here's how i solved this:

$query = Word::whereHas('typeswb', function($q)
         {
             $q->where('type_id', '=', post('typesofwordsearch')); //post('typesofwordsearch') return integer which are id's of types according to the table
         })->limit(5)->get();

Breakdown about how it is working:

So, there could be another way but after trying long, this one worked.

Firstly i am using my Word model where in the word model, i have typeswb which is defining belongToMany relation (see question for that). I am using whereHasread this for more info on that. Basically using wherehas i am instructing query to look up with relation. Afterwards, In function closure, I'm queries using one of key of my types table i.e type_id. For keys of types table again see my question above.

Hope that this will help someone. Cheers!

Ad
source: stackoverflow.com
Ad