Support the ongoing development of Laravel.io →
posted 10 years ago
Database
Last updated 2 years ago.
0

// 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"
                    }

                    ]
0

Sign in to participate in this thread!

Eventy

Your banner here too?

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.