Merge three collections and remove duplicates

I have three tables (customer_quotes, sales, posted_invoices) that each contain a company_code field.

I need an iterable of the distinct values to run through an update script.

How would I combine these three to only have a list of distincts?

$cqs = DB::table('customer_quotes')->select('company_code')->distinct()->get();

$sqs = DB::table('sales')->select('company_code')->distinct()->get();

$pqs = DB::table('posted_invoices')->select('company_code')->distinct()->get();

martinbean

5 years ago

Level 80

@jgravois I don’t really understand. List of distinct what? They’re rows from three separate tables.

jgravois

5 years ago

Level 33

the companies should be the same ... the ones we quote, sell to and invoice. However, there could be some quoted that never purchased etc. I need the full list of company_codes from those three tables without duplicates.

kima

5 years ago

Best Answer

Level 2

please try

        $cqs = DB::table('customer_quotes')->select('company_code');
        $sqs = DB::table('sales')->select('company_code');
        $pqs = DB::table('posted_invoices')->select('company_code');

        $unique = $cqs->union($sqs)->union($pqs)->distinct()->get();

tykus

5 years ago

Level 104

I believe you could UNION the three tables, producing a Collection of unique results:

$cqs = DB::table('customer_quotes')->select('company_code');

$sqs = DB::table('sales')->select('company_code');

$pqs = DB::table('posted_invoices')->select('company_code');


$result = $cqs->union($sqs)->union($pqs)->get();

Please or to participate in this conversation.