I have a sql query i want to convert it into query builder or Laravel ORM. It works fine. But i want it into query builder form or ORM. Is it possible to write in query builder or ORM ?.
I have four tables answers, questions, users and upvote_answers. Query has three ‘joins’ and one ‘left join’ just to check current logged in user upvoted(boolean) an answer or not along with other attributes
SELECT answers.answer_content as answer_content,
answers.id as answer_id,
answers.created_at as created_at,
answers.created_at as answer_upvote,
answers.created_at as answer_downvote,
questions.id as question_id,
questions.question_title as question_title,
questions.question_slug as question_slug,
users.id as user_id,
users.name as user_name,
users.user_slug as user_slug,
upvote_answers.upvote as upvote
FROM
answers
JOIN questions on questions.id = answers.question_id
JOIN users on users.id = answers.user_id
LEFT JOIN upvote_answers ON
upvote_answers.answer_id = answers.id AND
upvote_answers.user_id = '2'
WHERE
questions.question_active = 1 and
answers.answer_active = 1
Using query builder you could write your query as
DB::table('answers as a')
->join('questions as q', 'q.id', '=', 'a.question_id')
->join('users as u', 'u.id', '=', 'a.user_id')
->leftJoin('upvote_answers as ua', function ($join) {
$join->on('ua.answer_id', '=', 'a.id')
->where('ua.user_id', '=', 2);
})
->where('q.question_active', '=', 1)
->where('a.answer_active', '=', 1)
->select(DB::raw('a.answer_content as answer_content,a.id as answer_id,a.created_at as created_at,a.created_at as answer_upvote,a.created_at as answer_downvote,q.id as question_id, q.question_title as question_title, q.question_slug as question_slug, u.id as user_id, u.name as user_name, u.user_slug as user_slug,ua.upvote as upvote'))
->get();
Answer：
Use Join Clause
https://laravel.com/docs/master/queries#joins
Simple example
$users = DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->where('users.id', '=', 100)
->get();
Also you can use Left Join Clause.
Update:
$yourQuery = \DB::table('answers')
->join('questions', 'questions.id', '=', 'answers.question_id')
->join('users', 'users.id', '=', 'answers.user_id')
->leftJoin('upvote_answers', 'upvote_answers.answer_id', '=', 'answers.id')
->where('questions.question_active', '=', '1')
->where('answers.answer_active', '=', '1')
->selectRaw('answers.answer_content as answer_content,
answers.id as answer_id,
answers.created_at as created_at,
answers.created_at as answer_upvote,
answers.created_at as answer_downvote,
questions.id as question_id,
questions.question_title as question_title,
questions.question_slug as question_slug,
users.id as user_id,
users.name as user_name,
users.user_slug as user_slug,
upvote_answers.upvote as upvote');
echo $yourQuery->toSql(); //generated sql query as string
//$yourQuery->get(); //for get data from db
Result:
select answers.answer_content as answer_content, answers.id as
answer_id, answers.created_at as created_at, answers.created_at as
answer_upvote, answers.created_at as answer_downvote, questions.id as
question_id, questions.question_title as question_title,
questions.question_slug as question_slug, users.id as user_id,
users.name as user_name, users.user_slug as user_slug,
upvote_answers.upvote as upvote from answers inner join questions on
questions.id = answers.question_id inner join users on users.id =
answers.user_id left join upvote_answers on upvote_answers.answer_id =
answers.id where questions.question_active = ? and
answers.answer_active = ?
If you need upvote_answers.user_id = '2' in leftJoin must use advance left join:
->leftJoin('upvote_answers', function($advancedLeftJoin){
$advancedLeftJoin->on('users.id', '=', 'contacts.user_id')
->where('upvote_answers.user_id', '=', 2);
})
Finally, The answer for your example is:
$yourQuery = \DB::table('answers')
->join('questions', 'questions.id', '=', 'answers.question_id')
->join('users', 'users.id', '=', 'answers.user_id')
->leftJoin('upvote_answers', function($advancedLeftJoin){
$advancedLeftJoin->on('users.id', '=', 'contacts.user_id')
->where('upvote_answers.user_id', '=', 2);
})
->where('questions.question_active', '=', '1')
->where('answers.answer_active', '=', '1')
->selectRaw('answers.answer_content as answer_content,
answers.id as answer_id,
answers.created_at as created_at,
answers.created_at as answer_upvote,
answers.created_at as answer_downvote,
questions.id as question_id,
questions.question_title as question_title,
questions.question_slug as question_slug,
users.id as user_id,
users.name as user_name,
users.user_slug as user_slug,
upvote_answers.upvote as upvote');
echo $yourQuery->toSql(); //generated sql query as string
//dd($yourQuery->get()); //result as collection
Result:
select answers.answer_content as answer_content, answers.id as
answer_id, answers.created_at as created_at, answers.created_at as
answer_upvote, answers.created_at as answer_downvote, questions.id as
question_id, questions.question_title as question_title,
questions.question_slug as question_slug, users.id as user_id,
users.name as user_name, users.user_slug as user_slug,
upvote_answers.upvote as upvote from answers inner join questions on
questions.id = answers.question_id inner join users on users.id =
answers.user_id left join upvote_answers on users.id =
contacts.user_id and upvote_answers.user_id = ? where
questions.question_active = ? and answers.answer_active = ?
? value bind after use ->get()