mercredi 23 septembre 2015

Convert Mysql Query to Laravel Eloquent

I am using Laravel 5.1 in my application.

My MySQL Query is

SELECT * FROM (SELECT uu.*,t.left_id AS meb_id,(CAST(t.pair AS UNSIGNED) - IFNULL(p.pair,0)) AS pair FROM (SELECT left_id,
(CASE 
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000') >= (SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2) 
THEN (SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000')
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000') < (SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2) 
THEN ((SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000') /2)
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000') >= (SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2)
THEN (SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000')
WHEN ((SELECT COUNT(meb_id) FROM user_count WHERE right_id=u.left_id AND active = 1 AND join_date <= '1442255400000') < (SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000')*2)
THEN ((SELECT COUNT(meb_id) FROM user_count WHERE left_id=u.left_id AND active = 1 AND join_date <= '1442255400000') / 2) END
) AS pair
FROM user_count AS u WHERE  left_id <> 0 GROUP BY left_id) AS t
LEFT JOIN users AS uu ON uu.`id` = t.left_id
LEFT JOIN `total_payment` p ON t.left_id = p.`meb_id`
WHERE t.pair <> 0) AS f WHERE f.pair > 0  AND f.active = 1

How Can I convert it in Laravel Eloquent? I don't know how to use when case in laravel eloquent.

My Database schema is like this

user_count table enter image description here

user table enter image description here

total_payment table

enter image description here

Can anyone help me please?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire