SQL join issue with Laravel

Question

i have 2 tables where First table have all the values of category but i dont want to show all categories to every customer so there is a column is_hidden the structure is

cat_id    cat_name    is_hidden
  1         cat         no
  2         cat1        no
  3         cat2        no
  4         cat3        no
  5         cat4        yes
  6         cat5        yes

Now in my SQL query i am getting all the values where is_hidden column is no In my other table i am approving some clients to show the category The table structure is

id   cat_id    client_id
1     5          1

Now i want to display all the campaign from table where is_hidden is no and now that i have approved the client in table 2 so it should also show the category id from 5

Now how can i join the SQL to get the desired result.

Here is my code what i have tried.

Approvals::where('client_id',$client_id)
            ->rightJoin('tbl_cat', function ($join) {
                $join->on('tbl_cat.cat_id', '=', 'tbl_approval.approval_id');
                $join->where('tbl_cat.is_hidden','=','no');                
            });

But i am not getting the category 5 in output.

don't use joins. laravel.com/docs/5.5/eloquent-relationships — Philipp Sander
– Philipp Sander, Commented Sep 25, 2017 at 9:07
@PhilippSander can you guide me how can i do achieve it with relationships — mohit
– mohit, Commented Sep 25, 2017 at 9:09
@sunitiyadav i think the rightjoin wont work because in where status i am not getting the category 5 — mohit
– mohit, Commented Sep 25, 2017 at 9:15

Hardeep Singh · Accepted Answer · 2017-09-25 10:04:06Z

1

this can't be achieve by join. you should use union all like following

SELECT
    *
FROM
    table1
WHERE 
    is_hidden = 'no'
UNION ALL
    SELECT
        t.*
    FROM
        table2 t2 INNER JOIN table1 t ON t.id = t2.cat_id
    WHERE
        t2.client_id = 1

answered Sep 25, 2017 at 10:04

Hardeep Singh

7881 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mohit Over a year ago

Can you convert this query to laravel eloquent. i am getting error when i am changing it to laravel query

Nikita · Accepted Answer · 2017-09-25 10:08:25Z

0

Your join should be on cat_id so that you will get records of cat_id 5. Hope it will help you.

Approvals::where('client_id',$client_id)
            ->rightJoin('tbl_cat', function ($join) {
                $join->on('tbl_cat.cat_id', '=', 'tbl_approval.cat_id');
                $join->where('tbl_cat.is_hidden','=','no');                
            });

answered Sep 25, 2017 at 10:08

Nikita

4372 silver badges12 bronze badges

Collectives™ on Stack Overflow

SQL join issue with Laravel

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related