1

I am having an issue in selecting specific fields from multiple tables using unionAll() in laravel 5.2.

This is an example of what I am doing:

$test1 = \DB::table('test1')->select('test1.first as example', 'test1.timestamp')->orderBy('timestamp', 'desc');
$test2 = \DB::table('test2')->select('test2.second as example2', 'test2.timestamp')->orderBy('timestamp', 'desc');
$test3 = \DB::table('test3')->select('test3.third as example3', 'test3.timestamp')->orderBy('timestamp', 'desc');

$testArray = $test1->unionAll($test2)->unionAll($test3)->get()->toArray();

So in this example it would return an array with a bunch of results, the issue comes in when the array is created.

The key for every sub array is going to be example, rather than example for the first table results, example2 for second table results and example3 for third table results.

I need it returned with valid keys such as:

[{"example: test1", "timestamp: 11-22-33"}{"example2: test2", "timestamp: 11-22-33"}{"example3: test3", "timestamp: 11-22-33"}]

But as I said, my union statements seem to be returning only the first example and timestamp keys for all sub arrays like this:

[{"example: test1", "timestamp: 11-22-33"}{"example: test2", "timestamp: 11-22-33"}{"example: test3", "timestamp: 11-22-33"}]
LF00
  • 27,015
  • 29
  • 156
  • 295
In the stars
  • 253
  • 4
  • 17
  • check this http://stackoverflow.com/questions/25924592/laravel-multiple-unions – Sachith Muhandiram Nov 30 '16 at 02:53
  • 1
    Don't know where in that post has information relating to my question. Can you point me to which response or similar? Keep in mind, my union statements work fine, no errors etc, it's just how the array key is being defined that I have a problem with. – In the stars Nov 30 '16 at 02:59
  • does `$test1` has values when its executed? – Sachith Muhandiram Nov 30 '16 at 03:00
  • $test1 is a built statement, same with $test2 and $test3. First query is executed with $test1, then $test2 and $test3 use unionAll to merge – In the stars Nov 30 '16 at 03:02
  • yes, do they have values after initial query? `$test1 = \DB::table('test1')->select('test1.first as example', 'test1.timestamp')->orderBy('timestamp', 'desc');` what does `$test1` hold? – Sachith Muhandiram Nov 30 '16 at 03:04
  • ...nothing other than the built query. Laravel allows you to build queries and then execute them. $test1 holds a query that hasn't been run yet, it gets run with this $testArray = $test1->unionAll($test2)->unionAll($test3)->get()->toArray(); – In the stars Nov 30 '16 at 03:06
  • toArray() is not a member function of query builder, is of the elequent builder. – LF00 Nov 30 '16 at 03:38

1 Answers1

0

Try this :

$test1 = \DB::table('test1')->select('test1.first as example', 'test1.timestamp')->orderBy('timestamp', 'desc');
$test2 = \DB::table('test2')->select('test2.second as example2', 'test2.timestamp')->orderBy('timestamp', 'desc');
$test3 = \DB::table('test3')->select('test3.third as example3', 'test3.timestamp')->orderBy('timestamp', 'desc');

$testArray = $testArray->unionAll($test1)->unionAll($test2)->unionAll($test3);

$arrays = $testArray->get()->toArray();

reference

Community
  • 1
  • 1
Sachith Muhandiram
  • 2,819
  • 10
  • 45
  • 94
  • Ya, you have a build or place a built query statement previous to unionAll() or union() I believe. As kris stated, $testArray can't utilize ->unionAll() as it's not a built query. – In the stars Nov 30 '16 at 03:35