wpd bzw. query vars --> order by --> MB Custom Table

This topic has 8 replies, 2 voices, and was last updated 1 year, 11 months ago by Peter.

Viewing 9 posts - 1 through 9 (of 9 total)

Author

Posts
December 12, 2022 at 1:09 AM #39694
jw_bln
Participant
Some hints regarding custom queries or query vars are given (e.g. Link or in some support-forum entries), but they are not clear.

I am trying to create a custom query loop (in bricks builder) using MB Custom Table.
Generally, it is working using a filter, but *not* in case of "orderby".

I was trying doing it with wpdb:
```
global $wpdb, $ids;
$ids = $wpdb->get_col( "SELECT id FROM mytable WHERE myfield = 'news' ORDER BY mb_custom_table_field ASC" );
```
or alternatively with query vars
```
$query_vars['meta_key'] = 'mb_custom_table_field';
$query_vars['orderby']  = 'meta_value';
$query_vars['order']    = 'ASC';
```
Both are not working.
Please can you
a) explain what is possible
b) create an example showing the usage of Order by textfield

Thanks already in advance for your feedback.

Add New Note to this Reply
December 12, 2022 at 11:34 PM #39726

Peter
Moderator

Hello,

I think the SQL query will work

$ids = $wpdb->get_col( "SELECT ID FROM mytable WHERE myfield1 = 'news' ORDER BY my_field2 ASC" );

make sure the columns myfield1 and myfield2 are available in the custom table. Then you can print out the variable $ids to see how it works.

Add New Note to this Reply

December 14, 2022 at 4:26 PM #39777

jw_bln
Participant

Hi,
based on your answer I was asking the guys at Bricksbuilder.
I want to loop trough the Metabox meta fields (which is not an issue) AND order it based on such a Metabox meta field stored in the automatically created table.
They say Metaabox this:
" If your CPT is created using Metabox Custom Table, I am afraid they might not support WP_Query orderby parameter."

Please confirm 100% that your Plugin is able to order any custom field (based on MB Custom Table).
Thanks in advance.

Add New Note to this Reply

December 14, 2022 at 4:33 PM #39778

jw_bln
Participant

BTW: using print_r I see that the SQL itself is working and I am able to order those entries..
But looping through ordered WP_Query entries is not working, the related answer from them:
My QUESTION: ORDER BY is working (see the result of print_r). BUT: in Frontend no ordering takes place.
ANSWER Brisksbuilder: This is correct, those are 2 different querires, so order by in query 1 (SQL based) will not bring over to query in Bricks query.

Add New Note to this Reply

December 14, 2022 at 11:14 PM #39799
Peter
Moderator
Hello,

It's not possible to order by field value with WP Query, and it is noted in the documentation
```
It's important to understand that the plugin doesn't hook to the WP_Query to get posts by custom fields stored in the custom table. In other words, using meta_* in WP_Query for custom fields won't work.

To get posts by custom fields in the custom table, you need to make an extra query to get the post IDs first. Then use these IDs to get full post objects.
```
That means, if you use the custom table to store custom field value, you need to use the SQL query to get the post ID based on the field value.

Please read more here https://docs.metabox.io/extensions/mb-custom-table/#query-posts-with-wp_query

Add New Note to this Reply
December 15, 2022 at 12:46 AM #39804

jw_bln
Participant

Ok, understood - so it is not possible to use Metabox Custom Table and Bricksbuilder (and most likely Oxygenbuilder too) as Metabox custom table cannot support "orderby" parameter inside WP_Query.

As said, oderby in SQL is not sufficient...

THIS IS NOT WORKING:
// SQL to get relevant post-ID's (specific MB table)
global $wpdb, $ids;
$ids = $wpdb->get_col( " SELECT id FROM table WHERE select_category = 'xxx' ORDER BY MB_field DESC " );
// filter bricks custom loop
add_filter( 'bricks/posts/query_vars', 'custom_query', 10, 3 );
function custom_query( $query_vars, $settings, $element_id ) {
global $ids;
// apply only to #id of looping div
if ($element_id == 'xxxxx') {
// query vars
$query_vars['post__in'] = $ids;
return $query_vars;
}
}

Add New Note to this Reply

December 15, 2022 at 11:07 PM #39832
Peter
Moderator
Hello,

In your previous reply, you said that
BTW: using print_r I see that the SQL itself is working and I am able to order those entries..

So I think this query gets the post IDs correctly.
```
// SQL to get relevant post-ID's (specific MB table)
global $wpdb, $ids;
$ids = $wpdb->get_col( " SELECT ID FROM table WHERE select_category = 'xxx' ORDER BY MB_field DESC " );
```
Can you please confirm this?

Add New Note to this Reply
December 16, 2022 at 4:28 AM #39842

jw_bln
Participant

YES, fully correct - I can confirm that the output of wpdb and the print_r output shows that the correct data is selected and even ordered (if ORDER BY is used) - no surprise.

But on the front end/Bricksbuilder-UI, I don't see the correct result.
Bricksbuilder support points in your direction.

Shall I send you a message via the support-email, so you can see the conversation and feedback?

Add New Note to this Reply

December 17, 2022 at 5:05 PM #39885

Peter
Moderator

Hello,

Thank you but there is nothing we can do from our side at the filter hook of the Bricks builder, they should take a look at their filter support. It is also beyond our scope of support which you can see here https://support.metabox.io/topic/support-policy/

Thanks for your understanding and patience.

Add New Note to this Reply
Author

Posts

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic.