Thread

Hello

I have two weblogs events and adventure_events. Each have a different set of custom fields.

The events weblog has a custom date field named event_start_date.
The adventure_events weblog has a custom date field named adventure_start_date.

Can I use the weblog:entries tag to bring back entries from both events|adventure_events weblogs orderby (event_start_date OR adventure_start_date)?

If I do this orderby=“event_start_date|adventure_start_date” the results are not mixed together. Instead all the events entries are grouped first followed by all the adventure_events entries.

I’m trying to get all entries from both weblogs sorted together by date. But the two weblogs don’t share one common custom date field.

Thank you for any assistance.

ipatrick,

As a workaround you could use the entry date as opposed to a custom field for your start date. Then all you have to do is order by date.

{exp:weblog:entries weblog="events|adventure_events" limit="15" orderby="date"}

This is always posited as a solution. when i was looking to do this i though ok, lets do that.
The problem is when you get to the publish page the date section reads entry date rather than start date, which confuses the user.

I am working on something similar using a php solution that seems to work, I posted it for views but didnt get much response, i’m not certain how much overhead this would cause on a large number of posts or even if php is the way to go or perhaps a sql query.

http://ellislab.com/forums/viewthread/161773/

ipatrick, does that solve or at least explain your issue?

Hello

@John

Hey John I know about using the orderby=date but in this case I need to order by a custom field. Which I can do with a single weblog and a single custom field. This works great:

{exp:weblog:entries weblog="events"  disable="catgories|member_data|pagination|trackbacks" 
orderby="event_start_date" sort="asc" dynamic="off" }

It’s only when I want to add a second weblog which has it’s own custom field for start_date.

{exp:weblog:entries weblog="events|adventure_events"  
disable="catgories|member_data|pagination|trackbacks" 
orderby="event_start_date|adventure_start_date" sort="asc" dynamic="off" }

This will return the entries but not intermixed. Instead they come back like this.

event - jan
event - feb
event - march
event - march
adventure-event - jan
adventure-event - feb
adventure-event - feb
adventure-event - march

@joemo

If you’re just looking for getting a range of entries based on a single custom date field try the Solspace add-on Date Field Filter. http://www.solspace.com/software/detail/date_field_filter/

@Ingmar

Can you tell me if getting the results to be ordered by two custom date fields is beyond the functionality of EE 1.6.9?

Where the entries would be combined in a date order.

event - jan 1
adventure-event - jan 20
adventure-event - feb 6
event - feb 8
adventure-event - feb 26
event - march 3
event - march 7
adventure-event - march 17

ipatrick, you would want to use a custom query to achieve that.

Thank you

I’ll try going that route.

Hi ipatrick,

Ok, let us know how you get on.

Cheers

Greg

Here is a custom query you can use.  It is not real robust since it assumes an empty date field will have a ‘0’ in it as opposed to NULL.  If you get a NULL where dates are not set, then you can use COALESCE(value,...) in place of GREATEST(value,..).  Simply place the channel_id numbers and channel field names where you need them.  If you are using 1.x then you’ll have to adjust table names as well.  You’ll also need to change sort order to match your needs.

SELECT channel_id,GREATEST(field_id_4,field_id_11) as event_date 
FROM exp_channel_data NATURAL JOIN exp_channel_titles
WHERE channel_id='1' OR channel_id='2'
ORDER BY event_date ASC

Hope it works!

Hi trenchard,

Thanks very much for the assist.

@ipatrick, does the query trenchard posted help you out?

Cheers

Greg

Hello trenchard

Thank you soooooo much! After modifying your suggested SQL code into EE1 it worked great.

I have one follow up question about the date values that get returned. What is the data type of these values? Are they SQL timestamps? Or something internal to EE 1.6.9?

@Greg Salt

Yes my question has been successfully answered. Thanks

Thank you soooooo much! After modifying your suggested SQL code into EE1 it worked great.

Excellent.  We sometimes forget that we have the full power of MySQL Select at our disposal within our templates. 

What is the data type of these values? Are they SQL timestamps? Or something internal to EE 1.6.9?

EE stores dates in the database as UTC integers.  The data type of the fields are int(10). I’m sure the EE support team can be more explicit about how the UTC integers are created and consumed in the application code.

Glad to see this issue has been resolved. Don’t hesitate to start a new thread in case there’s anything else.