How to find combinations in a single table in Symfony

154
May 10, 2018, at 11:04 AM

I want to find all customers who have bought a toothbrush, a trampoline and a tomahawk in a single order. I could just fetch all order items and filter them in PHP, but that seems inefficient and a waste of the power of having a database that's built for matching.

I wrote a stored procedure for it, but then I couldn't get Doctrine to use it without just writing a MySQL query and running it directly.

item_id | order_id (fk) | item_name
--------+---------------+----------
order_id | customer_id (fk)
---------+-----------------
customer_id | customer_name
------------+--------------

The query is:

SELECT c.customer_id, c.customer_name
FROM customer c
JOIN order o
  ON o.customer_id = c.customer_id
JOIN item i1
  ON i1.order_id = o.order_id
JOIN item i2
  ON i2.order_id = o.order_id
JOIN item i3
  ON i3.order_id = o.order_id
WHERE i1.item_name = 'toothbrush'
  AND i2.item_name = 'trampoline'
  AND i3.item_name = 'tomahawk'
GROUP BY c.customer_id

Can I get the same effect in Symfony 2.8 through Doctrine or do I just have to write a whole bunch of MySQL?

Rent Charter Buses Company
READ ALSO
Issue printing timestamps from MySQL

Issue printing timestamps from MySQL

I have a MySQL table with 3 columns: "node", "busy", and "reg_date"I'm trying to get a PHP script to render the entire table in a web page

127
JetBrains (PhpStorm) IDE's color scheme rule for specific word

JetBrains (PhpStorm) IDE's color scheme rule for specific word

I've been experimenting with Color Schemas in PhpStorm and I would like to apply a certain color rule specifically for $this keywordIs this even possible in PhpStorm?

194
When storing large JSON files it more efficient to store them in a database or as a file on a server?

When storing large JSON files it more efficient to store them in a database or as a file on a server?

My reason for asking is I'm thinking of building a component-based CMS app (kind of) and building said components as a JSON object from drag-and-drop (again kind of)

131
Mustache.php rendering multi-dimensional data

Mustache.php rendering multi-dimensional data

I'm utilizing Mustache to template some XML responses for an APII was wondering how I could use the XML template below to render data from this array?

121