How to add dataobject when fire a database query abd how use $object variable?

Questions
1

S4.2:

How to add dataobject when firing a database query:

$getCat = “SELECT * FROM Devmac_Product WHERE ProductCategory REGEXP ‘(32)’”;
$objResult = DB::query($getCat);

// I$f->addFieldToTab(
            'Root.Main',
            GridField::create(
                'Product',
                'Product List',
                $objResult,
                GridFieldConfig_RecordEditor::create()
            )
            ->setRightTitle('Editing these products also effects the master list')
        );
2

I’m slightly confused what you’re asking here. Can you provide any more information?

The use of the REGEX and DB queries might not be the best way to do it… depending on what you’re trying.

If ‘Devmac_Product’ is a DataObject, then you’re better off using the standard methods to get that, eg.:

$products = Devmac_Product::get()->filter(['ProductCategory' => 32]);

If you can tell us what you want to do, we might be able to provide a bit more info.

3

I try to get all Products from a table where ProductCategory data match with the id 32.

SELECT * FROM Devmac_Product WHERE ProductCategory REGEXP ‘(32)

here,

  1. Devmac_Product is the Table name.
  2. ProductCategory is a column name in which store array of ID (i.e [“2”, “3”, “4”, “4”].
  3. To match ProductCategory with id I use REGEX.
  4. here, Product is a DataObject.

Yes, you are right your suggested format is best, but I don’t how to use Where Clause with IN condition.

4

If you’re using Silverstripe and these are DataObjects, then it would be well worth having a look at the documentation around data relations (https://docs.silverstripe.org/en/4/developer_guides/model/relations/)

I’d suggest that you might be better off storing the ProductCategory data as a DataObject as well, and then setting up a many_many relationship between them and the Products. That way, you’ll get the benefits of using the ORM properly, as well as being able to access lots of the data more easily in your templates. You’ll also be able to steer clear of the regex and writing your own queries.

5

I got a Solution…,
Here, ProductCategory store value is [“28”, “32”].
Sorry for the above mention ProductCategory Values…

Product::get()->filter(['ProductCategory:PartialMatch' => '32']);

Thank you for your suggestion.

6

Hi,

Whilst that might work, it’s not a very reliable way to deal with the issue.

Don’t forget… that will also match if 132, 232, 320, 332, etc. are in the string.

7

Hi, DorsetDigital. Thanks for that.
You have any other solution for that or any other way to do this.

8

The best advice I can offer is in my previous post. Set the categories up as a dataobject and create a relationship between them. That way you don’t have to worry about any of these things :slight_smile: