Distinguishing Row Tables During Union Queries

posted 10 years ago

Hi,

I'm currently using a union to jointly select search results from two tables. However, I'm struggling to implement a means of distinguishing which table each search result has come from, without performing an additional query to find the result in each of the tables.

Currently this is my Union query, using eloquent:

	$search = '%'.Input::get('Search').'%';
	$user_items = DB::table('user_items')
	->select('user_items.id as user_item_id', 'user_items.item_name', 'user_items.item_description')
	->where('user_items.item_name', 'LIKE', $search)
	->orWhere('user_items.item_description', 'LIKE', $search);
	

	$store_items = DB::table('store_items')
	->select('store_items.id as store_id', 'store_items.item_name', 'store_items.item_name')
	->where('store_items.item_name', 'LIKE', $search)
	->orWhere('store_items.item_description', 'LIKE', $search);

	$results = $user_items->union($store_items)->take(30)->get();
	var_dump($results);

As you can see, I've tried to distinguish the id by specifying it to be considered 'as' user_items_id and store_items_id respectively. However, when returning the results, all the id's are listed as 'user_item_id' I believe this to be a result of $user_items being the host to the union with $store_items.

I have found that I may be able to define the result 'type' in SQL using

	SELECT field1, field2, ... "Product" AS type
	SELECT field1, field2, ... "Service" AS type

Is there a way I can do this using eloquent? Should I be using DB::raw() ?

Any help would be greatly appreciated!

Last updated 2 years ago.

iansirkit

replied 10 years ago

If yopu want to use raw SQL you can use DB::raw() or DB::unprepared()

However another option would be to create a "view" in the database and use your raw SQL as the view definition, then create a model and use the view as it's table. This method will be better for selecting data but you will need special insert and update functions if you go this route.

IMO creating the view is the more elegant solution.

Last updated 2 years ago.

arcanewinds

replied 10 years ago

Thanks, I've looked into using views - and will probably implement those as an after-sight. Is there a means to achieve distinguishing tables using eloquent in this way however? I will use raw SQL to patch this for now, but I'm sure there should be a way to do this using eloquent.

Last updated 2 years ago.

arcanewinds

replied 10 years ago

Solution

For anyone having the same problem in future. I managed to solve this by adding an additional static 'type' record to the select query.

You can see this in use at the end of the select statements in the following:

	$user_items = DB::table('user_items')
	->select(DB::raw('user_items.id, user_items.item_name, user_items.item_description, "userItem" as type'))
	->where('user_items.item_name', 'LIKE', $search)
	->orWhere('user_items.item_description', 'LIKE', $search);
	

	$store_items = DB::table('store_items')
	->select(DB::raw('store_items.id, store_items.item_name, store_items.item_name, "storeItem" as type'))
	->where('store_items.item_name', 'LIKE', $search)
	->orWhere('store_items.item_description', 'LIKE', $search);
		
	$results = $user_items->union($store_items)->take(30)->get();

Last updated 2 years ago.

hashmatwaziri

replied 9 years ago

// Use PDO . It is the simplest answer I found after two days of struggling for using complex UNION in Laravel

 $PDO = DB::connection('mysql')->getPdo();

 $billingStmt = $PDO->prepare("

 select * from (SELECT   * 
 FROM     t_statements 
 WHERE    reference_id = $user_id 
 AND      service_provider='FOLDER' 
 AND      bill_name IS NOT NULL 
 ORDER BY bill_name ASC ) AS a 

 UNION ALL 

 SELECT * 
 FROM   ( 
 SELECT   * 
 FROM     t_statements 
 WHERE    reference_id = $user_id 
 AND      service_provider !='FOLDER' 
 AND      bill_name IS NOT NULL 
 ORDER BY (CASE WHEN is_paid = 0 THEN due_date ELSE is_paid END) DESC) b
 
     ");



        $billingStmt->execute();
        $usersBills = $billingStmt->fetchAll((\PDO::FETCH_ASSOC));
        header('Content-Type: application/json');
        $androidUserBills = json_encode($usersBills); // return results as json 

    return response($androidUserBills);

// JSON response

       [
             {
                  "id": "247",
              	  "created_at": "2016-02-23 10:44:33",
		          "updated_at": "2016-02-23 16:58:57",
		          "t_user_account_id": "245",
		          "statement_date": null,
		          "due_date": "0000-00-00 00:00:00",
		          "amount": "0",
		          "is_paid": "0",
		          "is_reminded": "1",
			      "overdue": null,
	              "current_amount": null,
		          "bill_total": "88.5",
		          "bill_id": "zd91NwGU",
                  "bill_name": "Utility",
                  "predecessor": null,
                  "reference_id": "120",
                  "service_provider": "FOLDER",
                  "sp_id": null
                   },
                 
             {
                  "id": "252",
                  "created_at": "2016-02-23 16:29:50",
                  "updated_at": "2016-02-23 16:58:25",
                  "t_user_account_id": "250",
                  "statement_date": null,
                  "due_date": "2016-03-04 17:52:34",
                  "amount": "0",
                  "is_paid": "0",
                  "is_reminded": "1",
                  "overdue": null,
                  "current_amount": null,
                  "bill_total": "88.5",
                  "bill_id": "Lojnc",
                  "bill_name": "Water bill",
                  "predecessor": null,
                  "reference_id": "120",
                  "service_provider": "IWK",
                  "sp_id": "7"
                    }

                    ]

Moderators