Can you give me a little bit of help with this?I want to convert this for laravel 5.2, and also add role_users table with column user_id and user_role. Can you help me please?
$career_solutions_data = DB::select("
SELECT
career_solutions.id,
career_solutions.subject,
career_solutions.date,
career_solutions.public,
career_solutions.views,
career_solutions.optional,
career_solutions.on_offer,
users.username,
users.profile_picture,
categories.category,
categories.category_url,
categories.color,
career_solutions_categories.category as sub_category
FROM career_solutions
INNER JOIN categories
ON categories.id = career_solutions.topic_category_id
INNER JOIN career_solutions_categories
ON career_solutions_categories.id = career_solutions.topic_subcategory_id
INNER JOIN users
ON users.id = career_solutions.user_id
INNER JOIN privacy_settings
ON privacy_settings.user_id = users.id
WHERE users.deleted_at IS NULL
AND (
(privacy_settings.career_solutions = 0 AND public = 1 )
OR (users.id IN (
SELECT contacts.contact_id
FROM contacts
WHERE contacts.user_id = $id
)
)
)
OR users.id = $id
ORDER BY date desc limit 5000
");
I'm not 100% sure on this, and it likely doesn't work.
BUT - it will give you a good start point on rebuilding this query in Eloquent.
There's probably a better way of doing this, but again, this is more for a starting point. You really should be trying to learn Eloquent better as it's a pretty powerful and fluent ORM. (It reads so much better than T-SQL!).
But it'll look something along these lines anyways:
CareerSolutions::with(
[
'categories',
'categories.career_solutions_categories',
'users',
'users.privacy_settings'
], function($query) {
$query->where('users.privacy_settings.career_solutions', 0);
$query->where('users.privacy_settings.public', 1);
$query->orWhereIn('contacts.contact_id', function() {
Contact::where('user_id', $id)->pluck('id');
});
}
)->orWhere('users.id', $id)->get();