Please, give us your table names with the relevan primary/foreign key column names. Probably senses and words...
Thank you for your interest, @Firtzberg!
Here you go:
I prefer to learn data structure by selecting example data - so, I hope you'd find it useful too. There are four queries explaining my goal. First I select wordid from words, then select all the senses (there can be many but only one found here) , then look for the synonyms for each sense and finally have my synonyms eagerly loaded:
select * from words where lemma = 'eloquent';
+--------+----------+
| wordid | lemma |
+--------+----------+
| 41769 | eloquent |
+--------+----------+
1 row in set (0.00 sec)
MariaDB [wordnet]> select * from senses where wordid = 41769\G
*************************** 1. row ***************************
wordid: 41769
casedwordid: NULL
synsetid: 300151773
senseid: 58835
sensenum: 1
lexid: 0
tagcount: 4
sensekey: eloquent%5:00:00:articulate:00
MariaDB [wordnet]> select wordid from senses where synsetid = 300151773 and wordid != 41769;
+--------+
| wordid |
+--------+
| 45432 |
| 49737 |
| 120045 |
| 120123 |
| 121750 |
+--------+
select * from words where wordid in (45432,49737,120045,120123,121750);
+--------+----------------+
| wordid | lemma |
+--------+----------------+
| 45432 | facile |
| 49737 | fluent |
| 120045 | silver |
| 120123 | silver-tongued |
| 121750 | smooth-spoken |
+--------+----------------+
Hope all this stuff is not too verbose.
Here is also an svg with full schema: http://wnsqlbuilder.sourceforge.net/images/uml-wordnet.svg
colshrapnel said:
Every example I am able to find uses constant value in condition but here I have to address the field name. Is it possible? I even have no idea how which name to use, as it's obviously ambiguous.
Yes it is possible with whereRaw.
colshrapnel said:
I've got a model with a relation (which represents Wordnet open source English dictiionary if anyone cares) which relates to itself as many-to many:
From the given schema and table columns I would draw the consolusion that there exists a Many-to-Many relationship between Word and Synset. The Sense is actually a pivot.
colshrapnel said:
and this relation is called from controller like this
$data = Sense::where('wordid', '=', $wordid) ->with(['synonyms']) ->get();
Define a One-to-Many relationship between Word and Sense instead of using Sense::where('wordid', '=', $wordid).
You just told me you want this query:
select * from words where wordid in (
select wordid from senses where wordid != ? and synsetid in (
select synsetid from senses where wordid = ?
)
)
The two '?'s are of same value.
This should work
Word::whereIn('wordid', function($query){
$query->select('wordid')->from('senses')
->where('wordid', '!=', $wordid)
->whereIn('synsetid', function($query){
$query->select('synsetid ')->from('senses')->where('wordid', '=', $wordid);
}
);
})->get();
If you want a 'Laravel friendly' solution, then get the Synsets of the Word and eagerload their Words.
$word = Word::find($id);
$SynSets = $word->synsets()->with('words')->get();
This won't solve your primary question to skip the $word itself, but this should be much clearer. I would prefer it.
I hope I helped.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community