|
DIgitalAdvantage
|
December 27, 2016 at 4:12 pm
Hello,
I have two custom meta keys (type & genre). I’m using EE4 model querying
to filter events, but only one is being used and the other is ignored. After inspecting the SQL generated, it seems that EE_Base does not automatically generate a SELF-JOIN, which how such case should be handled. Is there any native way of doing this in EE4?
$events = EEM_Event::instance()->get_all(array(
array(
‘AND’ => array(
‘Post_Meta.meta_value*a’=>’genre’,
‘Post_Meta.meta_value*b’=>’genre1’,
),
‘AND’ => array(
‘Post_Meta.meta_key*c’=>’type’,
‘Post_Meta.meta_value*d’=>’type1’,
)
)));
|
|
Josh
|
December 28, 2016 at 7:43 am
It does handle the joins, you can check the PHP doc block in the source for more information about how that query should be formed:
https://github.com/eventespresso/event-espresso-core/blob/master/core/db_models/EEM_Base.model.php#L785
|
|
DIgitalAdvantage
|
December 28, 2016 at 8:24 am
Hello Josh,
I tried using the “on_join_limit” but it doesn’t work, and I believe it won’t for two reasons. First, I need to do self join on wp_postmeta in this case there has to be two aliases Post_Meta1 and Post_Meta2 for it to work. Can you please help as this is urgent?
EEM_Event::instance()->get_all(array(
‘limit’ => array($offset,$limit),
‘order_by’ => array(‘Datetime.DTT_EVT_start’ => ‘ASC’),
‘on_join_limit’ => array( ‘Post_Meta’ , array($offset,$limit) ) ,
array(
‘AND’ => array(
‘Post_Meta.meta_value*a’=>’genre’,
‘Post_Meta.meta_value*b’=>’Singles’,
),
‘AND’ => array(
‘Post_Meta.meta_key*c’=>’type’,
‘Post_Meta.meta_value*d’=>’Free’,
)
)));
|
|
Josh
|
December 28, 2016 at 8:38 am
If you want to use EE4’s models for post meta, you can use the EEM_Extra_Meta model, which is documented here:
https://github.com/eventespresso/event-espresso-core/blob/master/docs/G–Model-System/using-ee4-model-objects.md#storing-meta-information-about-any-model-object
If you want to use wp_postmeta, then you can do a normal WP Query:
https://codex.wordpress.org/Class_Reference/WP_Query
|
|
DIgitalAdvantage
|
December 28, 2016 at 9:38 am
If I use EEM_Extra_Meta, is t possible to have the fields editable in the wp-admin event edit page? and if so how?
|
|
DIgitalAdvantage
|
December 28, 2016 at 10:47 am
Josh,
I used EE4 Extra Meta to save the extra fields as per your suggestions which took considerably alot of time to update. Yet still i’m facing the same problem with joins, KINDLY give this issue priority and try to understand the problem before suggesting irrelevant solutions.
$events = EEM_Event::instance()->get_all(array(
‘limit’ => array($offset,EEService::$limit),
‘order_by’ => array(‘Datetime.DTT_EVT_start’ => ‘ASC’),
array(
‘AND’ => array(
‘Extra_Meta.EXM_key*a’=>’crowd’,
‘Extra_Meta.EXM_value*b’=>’Singles’,
),
‘AND’ => array(
‘Extra_Meta.EXM_key*c’=>’type’,
‘Extra_Meta.EXM_value*d’=>’Free’,
)
)));
|
|
Josh
|
December 28, 2016 at 12:02 pm
Irrelevant solutions?
|
|
DIgitalAdvantage
|
December 28, 2016 at 12:13 pm
I tried the mentioned solution by using the Extra Meta but it didnt work simply because I’m trying to self join with the same table to filter Events using multiple (post meta keys and values) at the same time.
If i use WP_QUERY I would lose the ability to filter with venues since they are not attached to the default wordpress post.
So the only way to be able to filter is using the model system, now it works for all but for this exception case of trying to filter events by two custom keys using an “AND” operator doesn’t work.
I would appreciate your help on this 🙂
|
|
DIgitalAdvantage
|
December 28, 2016 at 12:26 pm
After reading the documentation for ‘on_join_limit’ it seems that it only works for Primary Table join in my case. The query is executed on the Events model and I want to do a self join on another table.
Is there a way that maybe I can create a custom SQL Query and somehow have this attached to an EE_Event object ?
|
|
Josh
|
December 28, 2016 at 12:33 pm
EEM_Event::instance()->get_all(
array(
array(
'Post_Meta.meta_key' => 'genre',
'Post_Meta.meta_value' => 'Singles',
'Post_Meta.meta_key*a' => 'type',
'Post_Meta.meta_value*a' => 'Free',
),
'limit' => array( $offset, $limit ),
'order_by' => array(
'Datetime.DTT_EVT_start' => 'ASC'
),
'on_join_limit' => array(
'Post_Meta',
array( $offset, $limit )
)
)
);
|
|
DIgitalAdvantage
|
December 28, 2016 at 12:43 pm
Thank you so much for you reply, I appreciate your help.
However I got the below error
"Unknown column 'Post_Meta.ID' in 'on clause'" while running wpdb method "get_results"
you can check the SQL Query generated
SELECT Event_CPT.ID AS 'Event_CPT.ID', Event_CPT.post_title AS 'Event_CPT.post_title', Event_CPT.post_content AS 'Event_CPT.post_content', Event_CPT.post_name AS 'Event_CPT.post_name', Event_CPT.post_date AS 'Event_CPT.post_date', Event_CPT.post_excerpt AS 'Event_CPT.post_excerpt', Event_CPT.post_modified AS 'Event_CPT.post_modified', Event_CPT.post_author AS 'Event_CPT.post_author', Event_CPT.post_parent AS 'Event_CPT.post_parent', Event_CPT.menu_order AS 'Event_CPT.menu_order', Event_CPT.post_status AS 'Event_CPT.post_status', Event_CPT.comment_status AS 'Event_CPT.comment_status', Event_CPT.ping_status AS 'Event_CPT.ping_status', Event_Meta.EVT_display_desc AS 'Event_Meta.EVT_display_desc', Event_Meta.EVT_display_ticket_selector AS 'Event_Meta.EVT_display_ticket_selector', Event_Meta.EVT_visible_on AS 'Event_Meta.EVT_visible_on', Event_Meta.EVT_additional_limit AS 'Event_Meta.EVT_additional_limit', Event_Meta.EVT_default_registration_status AS 'Event_Meta.EVT_default_registration_status', Event_Meta.EVT_member_only AS 'Event_Meta.EVT_member_only', Event_Meta.EVT_phone AS 'Event_Meta.EVT_phone', Event_Meta.EVT_allow_overflow AS 'Event_Meta.EVT_allow_overflow', Event_Meta.EVT_timezone_string AS 'Event_Meta.EVT_timezone_string', Event_Meta.EVT_external_URL AS 'Event_Meta.EVT_external_URL', Event_Meta.EVT_donations AS 'Event_Meta.EVT_donations', Event_CPT.ID AS 'Event_CPT.ID', Event_Meta.EVTM_ID AS 'Event_Meta.EVTM_ID', Post_Meta.meta_id AS 'Post_Meta.meta_id', Post_Meta.post_id AS 'Post_Meta.post_id', Post_Meta.meta_key AS 'Post_Meta.meta_key', Post_Meta.meta_value AS 'Post_Meta.meta_value', Datetime.DTT_ID AS 'Datetime.DTT_ID', Datetime.EVT_ID AS 'Datetime.EVT_ID', Datetime.DTT_name AS 'Datetime.DTT_name', Datetime.DTT_description AS 'Datetime.DTT_description', Datetime.DTT_EVT_start AS 'Datetime.DTT_EVT_start', Datetime.DTT_EVT_end AS 'Datetime.DTT_EVT_end', Datetime.DTT_reg_limit AS 'Datetime.DTT_reg_limit', Datetime.DTT_sold AS 'Datetime.DTT_sold', Datetime.DTT_reserved AS 'Datetime.DTT_reserved', Datetime.DTT_is_primary AS 'Datetime.DTT_is_primary', Datetime.DTT_order AS 'Datetime.DTT_order', Datetime.DTT_parent AS 'Datetime.DTT_parent', Datetime.DTT_deleted AS 'Datetime.DTT_deleted' FROM wp_posts AS Event_CPT LEFT JOIN wp_esp_event_meta AS Event_Meta ON Post_Meta.ID = Event_Meta.EVT_ID LEFT JOIN wp_postmeta AS Post_Meta ON Post_Meta.post_id=Event_CPT.ID LEFT JOIN wp_esp_datetime AS Datetime ON Datetime.EVT_ID=Event_CPT.ID WHERE Event_CPT.post_type = 'espresso_events' AND Event_CPT.post_status NOT IN ('auto-draft','trash') AND ( (Datetime.DTT_deleted = 0) OR Datetime.DTT_ID IS NULL) AND Post_Meta.meta_key = 'genre' AND Post_Meta.meta_value = 'Singles' AND Post_Meta.meta_key = 'type' AND Post_Meta.meta_value = 'Free' GROUP BY Event_CPT.ID ORDER BY Datetime.DTT_EVT_start ASC LIMIT 0,3
Thanks again
|
|
DIgitalAdvantage
|
December 28, 2016 at 12:44 pm
I cropped only the part with the problem below 🙂 thanks
FROM wp_posts AS Event_CPT LEFT JOIN wp_esp_event_meta AS Event_Meta ON Post_Meta.ID = Event_Meta.EVT_ID LEFT JOIN wp_postmeta AS Post_Meta ON Post_Meta.post_id=Event_CPT.ID LEFT JOIN wp_esp_datetime AS Datetime ON Datetime.EVT_ID=Event_CPT.ID
|
|
Josh
|
December 28, 2016 at 1:11 pm
What you could do then is use WP query to get the post IDs, then use those post IDs in the WHERE condition for the model query.
|
|
Josh
|
December 28, 2016 at 1:43 pm
Example:
$postlist = get_posts(array(
'numberposts' => -1,
'post_type' => 'espresso_events',
'meta_query' => array(
'relation' => 'AND',
array(
'key' => 'genre',
'value' => 'Singles',
'compare' => '=',
),
array(
'key' => 'type',
'value' => 'Free',
'compare' => '=',
),
),
));
$posts = array();
foreach( $postlist as $post ) {
$posts[] += $post->ID;
}
$events = EEM_Event::instance()->get_all(
array(
array ( 'AND' => array(
'EVT_ID' => array( 'IN', $posts )
) ),
'limit' => array($offset, $limit),
'order_by' => array(
'Datetime.DTT_EVT_start' => 'ASC'
)
)
);
foreach( $events as $event ) {
echo $event->name()."<br>";
}
|
