Developer forum

Forum » Integration » SQL query. Grouping customers bought items in a list

SQL query. Grouping customers bought items in a list

Jens Mouritzen
Jens Mouritzen
Reply

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...

orderlist.jpg

Replies

 
Jens Mouritzen
Jens Mouritzen
Reply

Anyone have an idea ?

 
Adrian Ursu
Adrian Ursu
Reply
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
Jens Mouritzen
Reply

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
Adrian Ursu
Reply

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
Reply

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
Adrian Ursu
Reply

I think this is a much more elegant solution :)

Adrian

 
Jens Mouritzen
Jens Mouritzen
Reply

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

 
Dmitriy Benyuk
Reply

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
Jens Mouritzen
Reply

How do i create a view in the database?

 
Jens Mouritzen
Jens Mouritzen
Reply

Anyone have an idea ?

 
Lars Larsen
Lars Larsen
Reply

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.

 

Capture.JPG

 

You must be logged in to post in the forum