Query object with specific value in properties array

Support
1

I have a custom product object that holds various properties in this format:

// properties column

a:3:{
  s:12:"manufacturer";
    s:15:"CstManufacturer";
  s:8:"supplier";
    s:11:"CstSupplier";
  s:3:"san";
    s:8:"12345678";
}

How can I query for an object that has e.g. a specific san value?

I know the regular way to get an object, but I don’t know how to specify a key inside the nested array in properties. The logic (in my head) would look something like this, but I guess it’s a bit more complicated:

$product = $modx->getObject('CustomProduct', ['properties'['san'] => '12345678']);
2

In your example, you have a serialized PHP array stored in your database (it’s stored as a string value inside the properties column).

You could potentially write a query that checks if the serialized string contains a specific substring, but this will probably lead to false positives and will become very complex.
Ideally, you should avoid storing serialized data in your database, especially if you’re going to need to query based on the contents of that data.

1 Like
3

I’ve done a quick fix like this:

$payments = $modx->getIterator('Booking\\Model\\bookingPayment', ['user' => $modx->user->get('id'), 'JSON_EXTRACT(configuration, "$.ticket_type") = "1"']);

Although you have a serialized array there, you might be able to make a regex for it?

but has @joshualuckers mentioned above, I moved the data to a custom table, which increased the speed quite a lot.

2 Likes
4

Thanks for that info. You probably could tell - I had no idea about serialized values.

I need to get specific objects for a script that updates my products based on csv data. So speed is not a big concern. It made me think though and instead of querying the needed object directly, I now create an array of all objects and the needed properties first, which I can then use to compare and update stuff with.

As this is part of Commerce, I can also make use of a few property methods which make this process a bit easier.

Thank you both for your input!

5

In this case, you might need to build actual SQL queries and use JSON_EXTRACT to match the JSON item you are looking for, but as @joshualuckers mentioned, be careful with this kind of data. Sometimes devs can de-normalize tables and crates JSON messes inside fields.

Here you can see some examples on how to run this type of queries:

1 Like
6

@camicase82 The data is a serialized array (created with the PHP function serialize), not JSON.
I don’t think you can use JSON_EXTRACT to query that.

1 Like
7

You are right, JSON_EXTRACT would only work in proper JSON