如何转换为laravel？

Question

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
");

Answer 1

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();