php - Laravel querying many to many records with eager loading conditions -
so app has structure so.
- payments hasandbelongstomany payers
- payers belongsto users (users hasmany payers)
i want query payments (and pivot data) user id. have been using query returns payments, regardless of user_id:
$payment_data = payment::with('payers')->get(); now, if want payments payers have user_id of, say, 5 tried this:
$payment_data = payment::with(array( 'payers' => function($q){ $q->where('user_id', '=', 5); }))->get(); but returns same result except condition applies payers, not payments i.e. still payments in payments table, not pivot data if user_id condition not fulfilled. can technically use result , filter out ones payers sub-array empty become inefficient large numbers of payments.
what's correct way form query?
edit
here basic representation of db structure:
and plain sql query (rdms varies idea):
select payments.*, payers_payments.* payments right join (payers left join payers_payments on payers.id = payers_payments.payer_id) on payments.id = payers_payments.payment_id payers.user_id = 5; using wherehas gives result:
select * "payments" (select count(*) "payers" inner join "payer_payment" on "payers"."id" = "payer_payment"."payer_id" "payer_payment"."payment_id" = "payments"."id" , "payers"."user_id" = '1') >= '1' which not want.
edit 2
ok after fiddling know need use eager loading need pivot fields fields payment model itself. here's example showing 3 payments , pivot data:
array ( [0] => array ( [id] => 2 [payment_date] => 2014-03-07 [company] => franco manca [item] => pizza [created_at] => 2014-03-10 10:16:08 [updated_at] => 2014-03-10 10:16:08 [payers] => array ( [0] => array ( [id] => 1 [name] => tim [email] => tim@tim.com [user_id] => 1 [created_at] => 2014-03-10 10:07:23 [updated_at] => 2014-03-10 10:07:23 [pivot] => array ( [payment_id] => 2 [payer_id] => 1 [amount] => 21.0 [pays] => 0 [created_at] => 2014-03-10 10:16:08 [updated_at] => 2014-03-10 10:27:45 ) ) [1] => array ( [id] => 2 [name] => tom [email] => tom@tom.com [user_id] => 1 [created_at] => 2014-03-10 10:16:35 [updated_at] => 2014-03-10 10:16:35 [pivot] => array ( [payment_id] => 2 [payer_id] => 2 [amount] => 0.0 [pays] => 1 [created_at] => 2014-03-10 10:27:45 [updated_at] => 2014-03-10 10:27:45 ) ) ) ) [1] => array ( [id] => 3 [payment_date] => 2014-03-05 [company] => kaff [item] => cocktail [created_at] => 2014-03-10 10:17:05 [updated_at] => 2014-03-10 10:17:05 [payers] => array ( [0] => array ( [id] => 1 [name] => tim [email] => tim@tim.com [user_id] => 1 [created_at] => 2014-03-10 10:07:23 [updated_at] => 2014-03-10 10:07:23 [pivot] => array ( [payment_id] => 3 [payer_id] => 1 [amount] => 12.0 [pays] => 1 [created_at] => 2014-03-10 10:17:05 [updated_at] => 2014-03-10 10:17:05 ) ) [1] => array ( [id] => 2 [name] => tom [email] => tom@tom.com [user_id] => 1 [created_at] => 2014-03-10 10:16:35 [updated_at] => 2014-03-10 10:16:35 [pivot] => array ( [payment_id] => 3 [payer_id] => 2 [amount] => 19.0 [pays] => 1 [created_at] => 2014-03-10 10:17:05 [updated_at] => 2014-03-10 10:17:05 ) ) ) ) ) the problem is, if change user_id condition be, say, 99999 (which doesn't exist), still returns payments payers array each being empty, when in fact should return empty result.
the 2 queries produced eager loading are:
select * "payments" --i want add user_id condition and
select "payers".*, "payer_payment"."payment_id" "pivot_payment_id", "payer_payment"."payer_id" "pivot_payer_id", "payer_payment"."amount" "pivot_amount", "payer_payment"."pays" "pivot_pays", "payer_payment"."created_at" "pivot_created_at", "payer_payment"."updated_at" "pivot_updated_at" "payers" inner join "payer_payment" on "payers"."id" = "payer_payment"."payer_id" "payer_payment"."payment_id" in ( '2', '3' ) , "user_id" = '1' --this satisfies condition
the method with used eager load relation, constraint on relation should set this:
$payment_data = payment::wherehas('payers', function($q){ $q->where('payers.user_id', '=', 5); })->get(); ); you can specify variable use in where clause:
$payment_data = payment::wherehas('payers', function($q) use ($foo){ $q->where('payers.user_id', '=', $foo); })->get(); );
Comments
Post a Comment