MySQL UNION and UNION ALL operators require that:

The same number of columns exist in all the statements that make up the UNION'd query. And the data types have to match at each position/column.

Yes i know this, that's why UNION won't work in my case, because i will be selecting alot of various columns.

But i would like to know what i do need to make it work :)

Not an expert, but,

what about populating a multidimensional array and do the job on php?

iboinas said:

Not an expert, but,

what about populating a multidimensional array and do the job on php?

Not an expert either, how could i achieve this? :P

Also what i find kinda odd, is that i can hardly find anything about this topic on internet. It's a commen functionality i would say, but impossible to find anything about it on internet.

You need to select NULL columns for non-existing columns in the table. In SQL you would do something like this too create dummy columns for your second larger table

SELECT
    tblA.id, tblA.name, NULL as url, NULL as author, tblA.category
FROM tblA
UNION SELECT
    tblB.id, tblB.random AS name, tblB.url, tblB.author, NULL as category
FROM tblB

Column names aren't important, however if you do not use the 'as' alias, the column names will come from the first select statement. So you will probably was to use the 'as' alias if you'r first select needs to create dummy columns for the second select.

If you do not want to map columns in one table to the columns in the other table (i.e. "tblpages.name" does not have an equivalent "name" column in the blogitems table). You will have to select the appropriate number of NULL columns at the correct place (so you would select 5 NULL columns at the END of your $pages query, and 2 NULL columns at the START of your $blogitems query).

Hopefully that makes sense, i'm generally better at explaining things vocally rather than in forum posts, but i hope i got the main idea across, feel free to ask any further questions on the topic if it doesnt make sense.

lol sorry :) before laravel i developed on pure php...

i don't think there's a query allowing you to have in same SQL result what u want... without giving it a try, at least... and even so, you want to pull differnte columns not existing on the other table (for both cases)

--> you can do it like answered above (having NULLS)

Why do you want to do it on the model side? Have you wondered to use it on controller?

Just grab the results you want, and decide on the controller which to use. Pull each one with a limit of 30. If both above 15, just use 15 of each.. Else just make your math... :)

Do i make any sense for your solution?

First of all, thank you for helping guys:)

noelmcavoy said:

I just looked over your post quickly but what kind of output are you producing? If it is a page or matching pages/posts then why couldn't you pass two collections(arrays) to the view and have it list the pages and posts separately?

Alternatively perhaps you could have each query return the id, result type (post or page), and the page name/entry title. This would give you 3 columns in each that could be combined into one set of results.

Hope this helps, let me know if I misunderstood your intended results.

When i read the first suggestion i was like "yeh.. why haven't i tried that out yet? xD" So will try this one first thanks! The second suggestion won't work out for me i think, since i want to search in more column then 1 in the post/blog.

leesherwood said:

You need to select NULL columns for non-existing columns in the table. In SQL you would do something like this too create dummy columns for your second larger table

SELECT
   tblA.id, tblA.name, NULL as url, NULL as author, tblA.category
FROM tblA
UNION SELECT
   tblB.id, tblB.random AS name, tblB.url, tblB.author, NULL as category
FROM tblB

Column names aren't important, however if you do not use the 'as' alias, the column names will come from the first select statement. So you will probably was to use the 'as' alias if you'r first select needs to create dummy columns for the second select.

If you do not want to map columns in one table to the columns in the other table (i.e. "tblpages.name" does not have an equivalent "name" column in the blogitems table). You will have to select the appropriate number of NULL columns at the correct place (so you would select 5 NULL columns at the END of your $pages query, and 2 NULL columns at the START of your $blogitems query).

Hopefully that makes sense, i'm generally better at explaining things vocally rather than in forum posts, but i hope i got the main idea across, feel free to ask any further questions on the topic if it doesnt make sense.

I've read over this multiple times, but i don't quite get what you mean?

iboinas said:

lol sorry :) before laravel i developed on pure php...

i don't think there's a query allowing you to have in same SQL result what u want... without giving it a try, at least... and even so, you want to pull differnte columns not existing on the other table (for both cases)

--> you can do it like answered above (having NULLS)

Why do you want to do it on the model side? Have you wondered to use it on controller?

Just grab the results you want, and decide on the controller which to use. Pull each one with a limit of 30. If both above 15, just use 15 of each.. Else just make your math... :)

Do i make any sense for your solution?

I'm using it in the controller ? I don't think i quite understand what you mean xD

noelmcavoy said:

I just looked over your post quickly but what kind of output are you producing? If it is a page or matching pages/posts then why couldn't you pass two collections(arrays) to the view and have it list the pages and posts separately?

Alternatively perhaps you could have each query return the id, result type (post or page), and the page name/entry title. This would give you 3 columns in each that could be combined into one set of results.

Hope this helps, let me know if I misunderstood your intended results.

Your first suggestion was the fix for me :) thank you very much ! ^^

Hi, this is a little off topic, but you should sanitize the search criteria from the GET array. I was working on a similar Search functionality recently and I used e(Input::get('search')) to sanitize the search criteria. I think e() is equal to HTML:: entities(). Without that, someone can inject JS in your code.

I think leesherwood was trying to say that you need to have the same number of columns in both SELECT statements for the UNION to work. He used NULL in order to mimic the existence of columns that are not there in the smaller table (less columns). Src: http://www.mysqltutorial.org/sql-union-mysql.aspx

If Eloquent methods can't meet your needs, you can also execute the whole query in raw format.

thanks.

It's a commen functionality i would say, but impossible to find anything about it on internet.????