Query Commerce Database

Janakiram

Vote:

How to get product and variant details using sql query directly from commerce database?

Would like to know related tables also.

#216363

Edited, Feb 05, 2020 7:01

Joel Yourstone

Vote:

Not recommended and not documented. Why do you want to do it? :)

#216370

Feb 05, 2020 7:52

Janakiram - Feb 05, 2020 8:03

My client requirement is such, need to find a way to generate a query.

Joel Yourstone - Feb 05, 2020 8:06

You have customer requirements that dictate which way you fetch your data? Why not use the APIs for it?

Quan Mai

Vote:

We don't disclose database schema, so basically it's blackbox to you. One of the reason is that maintaining database backward compatible is a very tedious task. Your script might stop working in the next update. Also you are missing all the cache.

What do you want to do with the product and variant details and why do you want to use direct database query? With proper fetching/caching strategy you can get adaquate, if not very good performance from well tested and well documented APIs.

#216374

Feb 05, 2020 8:12

Janakiram - Feb 05, 2020 9:19

Most probably the client wants this data to use in Business Intelligence.

Joel Yourstone - Feb 05, 2020 10:09

Then it sounds like you should build a product export that exports the data you want to export to the BI system :)

Quan Mai - Feb 05, 2020 10:10

@Janakiram
that does not explain why do you want to use direct database query

Siddharth Gupta

Vote:

@Janakiram : Just my 2 cents, I think you can achieve this using the Commerce API. You can use the API to traverse across the catalog and get all the information you require, you could leverage the data any way you want (Push it into a table in a DB, reports).

#216420

Edited, Feb 05, 2020 23:25

Stefan Holm Olsen

Vote:

Hi Janakiram

You can read from the commerce database, if you like. But you will be on your own, and you need to dig into the tables yourself, because the databases are not documented or supported.

That being said, I recommend that you query the APIs and push the necessary data to your client's BI database. You can do so regularly (with a scheduled job) or when changes occur (hook into content events).

#216424

Feb 06, 2020 6:29

Quan Mai

Vote:

The recipe 1.2 can be helpful

#216427

Feb 06, 2020 8:11

Quan Mai - Feb 06, 2020 8:13

https://leanpub.com/epicommercerecipes/read_sample

Janakiram - Feb 06, 2020 8:55

Hi Quan, thanks for your reply. I am aware that read_sample and I have already created a job based on that. My client requirement is all about fetching the data instantly and one time use only. That's why they are insisting for a query.
Trying to convince them.

Quan Mai - Feb 06, 2020 9:00

if they are doing some BI then why it's important to be "instantly" ?