Danish Danish - 3 months ago 24
MySQL Question

Retrieve entries from table via pivot table Octobercms

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.

Answer Source

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 according to types 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 whereHas read 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!