Keep in mind that as far as SQL knows, your data array is just another string value. All the structural array components like i:1;a:2:{s:4: are just part of the string. You can include them in your RegExp pattern if appropriate. What’s also important is what does the array look like when the desired condition of an empty key3 occurs? Is key3 itself missing? Or is it still present and set to an empty string? It might be better to use a NOT REGEXP comparison.
FYI, this sort of query is very inefficient. If performance is important and you frequently make use of this query or there is a large amount of data to query, it may be beneficial to manage this sort of data in a custom table where more efficient queries can be constructed. Of course, the effort required to implement this scheme and how much data needs to be converted is also a factor to consider.
Hello @bcworkz,
a workaround that i applied is quering a “LIKE” comparison with value ‘s:4:”key3″;s:0’
i am concerning for the future performance too and i thought to check if key3 is empty when the array meta is saved and if its true then create a new meta key that will indicate that this post has a key3 empty
Finally, whats the point of using CMS Framework like WordPress if you must create custom tables and other methods that not supported by the CMS out of the box (just a thought)
Thanks
-
This reply was modified 6 years, 8 months ago by
The Three.
Then you need to be sure the data stays synced. Not ideal. It’d be better to break key3 itself out of the data array and maintain it as in individual meta key value. I’m unsure how feasible this would be with your setup though.
It’s not like queries wouldn’t work the way you have it, just that the performance would be poor. No framework can be everything to all comers. When you deviate from the mainstream, some customizing should be expected. In any case you are still utilizing the CMS framework with custom tables. Same DB, accessed through WP class methods. It’s just a lower level utilization.
@bcworkz can you give me a use case with custom table?
my array is a group of text fields
field #1: name
field #2: date
field #3: document number
user can create as many groups as he needs
final example:
array(
array(
'name' => 'some name',
'date' => '15-5-2019',
'docnumber' => '',
),
array(
'name' => 'some name #2',
'date' => '15-5-2019',
'docnumber' => 'AP19385',
),
array(
'name' => 'some name #3',
'date' => '17-5-2019',
'docnumber' => 'AP12335',
)
)
Your table could have columns like so:
post_id (bigint)
meta_name (text)
meta_date (datetime)
meta_docnum (text)
Any one post can have any number of rows entered and data is typically accessed through the associated post_id. You can also easily query all post_id values where meta_docnum is empty. By joining this table with the posts table in a query, you can get any necessary post data related to the post_id values that matched with empty meta_docnum values. Use wpdb class methods to make such queries.
If this is going to be specific for one site, you can manually create the table in phpMyAdmin and avoid needing to write the code to create the table that would only be used once.
