Level 2
Please help me out someone.
Aug 26, 2015
36
Level 2
Complex Eloquent Relationship DB Selections
HI all, please help me out with this problem.
I have a labs table and lab_test table. A Lab can have multiple tests.
i want only those labs having all tests given in array
public function getLabsHavingTests($tests)
{
// $tests is an array having test_ids
// $tests is like this => [1,3]
// should return only those labs having all tests giving in $tests
}
Level 50
@jsartisan Maybe try something like:
Lab::whereHas('tests', function ($q) uses ($tests) {
$q->whereIn('test_id', $tests);
})->get();
Level 2
Level 8
Lab::whereHas('tests', function ($q) uses ($tests) {
foreach($tests as $test){
$q->where('test_id', $test);
}
})->get();
1 like
Level 2
@veve286 i have tried this. It returns empty array.
because its looks for something lke this "select * from lab_test where test_id = 1 and test_id = 3" which doesn't make any sense.
Level 8
try this . If it donesn't work , show me your Lab and lab_test model.
Lab::whereHas('lab_test', function ($q) uses ($tests) {
foreach($tests as $test){
$q->where('id', $test);
}
})->get();
Level 2
Test.php
class Test extends Model{
protected $fillable = ['name', 'description'];
}
Lab.php
class Lab extends Model{
protected $fillable = ['name', 'location_id', 'phone','description'];
public function tests()
{
return $this->belongsToMany('App\Models\Test');
}
}
Level 2
@veve286 that didn't work. it was showing error.
Level 8
Is it work ?
Lab::whereHas('test', function ($q) uses ($tests) {
foreach($tests as $test){
$q->where('id', $test);
}
})->get();
Level 2
This gives this error : "Call to undefined method Illuminate\Database\Query\Builder::test()"
Level 54
So, Labs have many tests, and a single test belongs to one lab, right?
I think you've got your relationship set up wrong
2 likes
Level 8
sorry typo error. I forget 's' in whereHas
Lab::whereHas('tests', function ($q) uses ($tests) {
foreach($tests as $test){
$q->where('id', $test);
}
})->get();
Level 8
@willvincent yes u r right . His model is also wrong. Change ur model relation like this.
public function test()
{
return $this->hasMany('App\Models\Test');
}
Level 2
A Weird Error.
Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select count() as aggregate from labs where (select count() from tests inner join lab_test on tests.id = lab_test.test_id where lab_test.dc_id = labs.id and id = 1 and id = 3) >= 1)
Level 2
I'm not using the inverse relationship. So i didn't add that relationship. But since you asked, i added this in Test.php
public function labs()
{
return $this->belongsToMany('App\Models\Lab');
}
Level 8
sorry again. Try this . If it doesn't work i have no idea :)
Lab::whereHas('tests', function ($q) uses ($tests) {
foreach($tests as $test){
$q->where('test_id', $test);
}
})->get();
Level 8
Have you already changed your Lab relation to has Many ?
public function test()
{
return $this->hasMany('App\Models\Test');
}
Level 2
@veve286 i'm using a pivot table and that's why i used belongsToMany relationship.
Level 8
Ok got it
Try this
Lab::whereHas('tests', function ($q) uses ($tests) {
foreach($tests as $test){ $q->where('lab_test.test_id', $test); }
})->get();
Level 2
Giving Empty Array.
Level 41
Why would you create a pivot table for such a simple relationship? Unless we're missing something, willvincent is right. A lab can have multiple tests. Each test belongs to one lab. That is a one-to-many relationship.
Level 2
@thomaskim User is searching labs according to tests. So for e.g. "A blood test" can be available to multiple labs and a labs can have many tests. Thats why i needed a pivot table.
1 like
Level 8
show me ur pivot table as a screeshot .
Level 2
id | test_id | price | lab_id | discount | is_home_collectable | created_at | updated_at
here are the columns for the pivot table.
Level 8
i means screenshot of pivot table that contains data :D
Level 41
@jsartisan Oh okay. :) Try this then. Have you tried adjusting kfirba's post a little bit like this?
Lab::whereHas('tests', function ($q) uses ($tests) {
$q->whereIn('id', $tests);
})->get();
Level 2
i dont know how to upload the image here :P
Level 2
@thomaskim Not Working. 'id' column not found
Level 41
@jsartisan You don't have an id in your tests table? What is test_id referencing then?
Level 2
yes i have. test_id in lab_test is referencing to the id of test table.
lab_test.test_id --------> tests.id
Please or to participate in this conversation.