SQL query. Grouping customers bought items in a list

Jens Mouritzen

Posted on 05/02/2018 21:19:52

Hi i'm making a new website for a client.

I'm trying to make an SQL query that outputs a list of Ecommerce orders, based on bought products, make sense?

I got a SQL query that looks like this (from the old website)

SELECT * FROM joom2.fkwhv_virtuemart_orders orders

LEFT JOIN fkwhv_users users ON orders.virtuemart_user_id = users.id

LEFT JOIN fkwhv_virtuemart_order_items items ON orders.virtuemart_order_id = items.virtuemart_order_id

WHERE orders.created_on LIKE "%2017%"

AND orders.order_status = "S"

ORDER BY users.email

This SQL gives the following output showed on the attached file "orderlist.jpg" (some fields blurred, email addresses etc.)

Dows this make any sense, it's hard to describe...

Replies

Jens Mouritzen

Posted on 06/02/2018 21:34:28

Anyone have an idea ?

Adrian Ursu

Posted on 06/02/2018 21:46:33

Hi Jens, How are you planning on using this list? Front-end, back-end? I believe you have a few options: 1. Customer center module 2. Data List 3. Index + query Publisher. If you can give more details, I can recommend the most appropriate solution. Adrian

Jens Mouritzen

Posted on 06/02/2018 22:39:28

The whole list has to be exported as a CSV with the orders grouped like in the image attached. It will not be used in frontend.

Every line on the list is a repeat of a product, regardless wich order it came from.The list will be used to check participants (buyers) of products (workshops) on a festival.

Adrian Ursu

Posted on 06/02/2018 23:34:45

Hi Jens,

I don;t think you have an easy way to do it from the back-end.

You can always use DataList for creating your SQL but the DataList will only make the list available as an XML file.

I guess you can always use the front-end logic to expose your data list as a CSV and password protect the page.

Probably the easiest method is with DataList, a custom list template and CSV ContentType settings on the page.

That's how I would do it.

The more complex method is to create a DW App or Module that will give you access to this SQL from Back-end.

Adrian

Dmitriy Benyuk

Posted on 07/02/2018 09:23:55

Hi Jens,
maybe you could create a view in your database and then use the Data integration module to make a job with a "SQL/Dynamicweb View provider" as a source and CSV provider as a destination
that will export your view content to CSV file.
Regards, Dmitrij

Adrian Ursu

Posted on 07/02/2018 11:01:54

I think this is a much more elegant solution :)

Adrian

Jens Mouritzen

Posted on 09/02/2018 08:48:55

Sounds promising :) I will try that. Any examples ?

Dmitriy Benyuk

Posted on 09/02/2018 11:54:10

Hi Jens,
you can read more about Data integration module here and look for the SQL Provider with Views(here is some documentation about SQL provider which is very similar to view provider).
Regards, Dmitrij

Jens Mouritzen

Posted on 12/02/2018 11:45:18

How do i create a view in the database?

Jens Mouritzen

Posted on 13/02/2018 14:30:55

Anyone have an idea ?

Lars Larsen

Posted on 15/02/2018 09:21:40

Hi Jens

In SQL Server Management Studio you can from within the Object Explorer create a view by right clicking the View node (see screenshot). You could also create a view using a query where you use the CREATE VIEW T-SQL statement. A query can be created clicking "New query" in SQL Server Management Studio. Run the query by clicking "Execute" when you have coded the CREATE VIEW statement.

You must be logged in to post in the forum

Developer forum

SQL query. Grouping customers bought items in a list

Replies