Can Neo4J to make such a request?

Online store sells auto products. You need to look for a pair of featured products in the same category with the additional statistical constraints. Now my head is spinning options on MySQL and Neo4J. Looking at the chart performance, I'll need it Neo4J, but I don't understand how to implement the aggregate functions.

The data model
Each purchase has a date
In one purchase at least one product.
Each product in the purchase is the cost.
The cost of one item can vary because there is inflation, the dollar, and promotions.
Each product has at least one category.

From the point of SQL would be this model
CREATE TABLE `product` (
 `productId` INT(11) NOT NULL,
 PRIMARY KEY (`productId`),
);

CREATE TABLE `product_category` (
 `productId` INT(11) NOT NULL,
 `categoryId` INT(11) NOT NULL,
 PRIMARY KEY (`productId`,`categoryId`)
);

-- purchasePair is denormalized couple of purchases
-- Means that the product productId with the price price was in the same order with the goods
productPairId -- and there will be several such pairs in a single purchase
CREATE TABLE `purchasePair` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `productId` INT(11) NOT NULL,
 `purchasedAt` datetime NOT NULL,
 `productPairId` INT(11) NOT NULL,
 `price` decimal(13,2) NOT NULL,
 PRIMARY KEY (`id`)
);


In particular, I need to search for such a request:
For a specific article to find 5 items in the same categories, but the price was no less than this product and the first were the ones that buy more often in the last 30 days.

Example:
Were to buy:
1. "Headlight model 1" (category "electrical equipment") for the 1000R was purchased together with the "Signal" (electrical equipment) for the 500r
2. "Headlight model 2" (category "electrical equipment") for the 500r was purchased together with the "Coolant" (the fluid) for the 800R
3. "Headlight model 2" (category "electrical equipment") for the 500r was purchased together with the "Mat Set" (Salon) 1500R

Now if I make a query "vapor product "Signal for 500 rubles", I must return "Headlight model 1" because it is too "electrical", but the price is higher than the "Signal" and the purchase was in the last 30 days.

In SQL the query would be (approximately):
SELECT pc2.productId, -- find a couple's product
 (SELECT COUNT(id) -- to determine the number of sales
 FROM purchasePair pr
 WHERE ((pr.productId = pc.productId AND pr.productPairId = pc2.productId)
 OR -- that the sales were in a pair
 (pr.productId = pc2.productId AND pr.productId = pc.productId)
)
 AND pr.price > '500 roubles' -- and the price is more than the product
 AND purchasedAt > '30 days ago' -- not very long ago
 ) as cnt
FROM productCategory pc
JOIN productCategory pc2 ON pc2.categoryId = pc.categoryId -- and that the goods were in the same category
WHERE pc.productId = 'Signal'
ORDER BY cnt DESC -- in descending order of number of sales
LIMIT 5 -- only 5 results found


How to do the same on Neo4J?
June 14th 19 at 20:11
2 answers
June 14th 19 at 20:13
Solution
You need more search engine Lucene/Solr/Elasticsearch

And based on it, implement market search or podrugomu speaking fuzzy search, not only by rows as it usually presenece, and combined function of all the site.

What you sobiraetes to do is recomedations search.
He also solved and Neo4j, but the speed will be less. And he well not quite for this.

Here I have provided links
On what principle are Advisory services?

And Yes, if I were to answer directly to your question, Yes you can.
Neo4J's Lucene - Julianne_Muller commented on June 14th 19 at 20:16
Yes , but it's a library and Neo4j she wrapped not as in Solr. - barney.Schroeder90 commented on June 14th 19 at 20:19
where to learn about using Solr queries similar to the one defined in this question? That was the "LAST 30 days" and "PRICE > X" and "ORDER BY MATCH COUNT" and "the same category" ? - Julianne_Muller commented on June 14th 19 at 20:22
By the way, the search strings do not need, I have all the numbers. If it comes to that. - Julianne_Muller commented on June 14th 19 at 20:25
, Best off. documentation lucene.apache.org/solr/guide/7_0/solr-tutorial.html

And not off. to make it easier to enter www.solrtutorial.com/solr-query-syntax.html

If row is not there, then I think that preimuschestv from Solr not so much. In addition to being free and larger kommuniti, only able to deplot in any servlet container.
But also have embedded Neo4j, it's up to you.

In any case have to keep poiskovye data separately from the main. - barney.Schroeder90 commented on June 14th 19 at 20:28
You need more search engine Lucene/Solr/Elasticsearch

- not true, as there is a query to structured data, and full-text engines with the structures all bad.
you are right look at neo4j. - Gerda.Boyer commented on June 14th 19 at 20:31
Interesting point, but parry, I naturally will not. - barney.Schroeder90 commented on June 14th 19 at 20:34

> He just solved and Neo4j, but the speed will be less.
And I thought, faster than Neo4j will be gone. So say, Solr can also do queries on data structures and count the units? Then there is room to grow in performance. - Julianne_Muller commented on June 14th 19 at 20:37
,
We have 2 directions of planar and non-planar structures. A collection of documents, also the structure of the data. Flat, if we disregard internal documents.

If imelos in mind that you can efficiently do queries in Solr to count, to non-planar structure, then no, I have not claimed.
He has the support graph, but it miserabily, compared to Neo4j .
Well so no one says that it should be in the graph. And what you have is reflected in the tables is not necessary one to one in a "pour".

If you will search Osnovnoy principle recomendation systems based on piskovyi indexes is selecting similar objects.
And scoring to fit the needs of a particular user plus commercial needs. any position to lift, some to lower.

In your case will be a fuzzy search by example. Will be the attributes which can be anything, and there will be those who varierade impossible.

As for me, if you want to vyplati the relationship between purchases and dopolnitelnoe knowledge to them that Neo4j is the best choice.
If you want to make recommendations online priblizheniem real time, only a flat structure and investirovanii search index, not the column. - barney.Schroeder90 commented on June 14th 19 at 20:40
June 14th 19 at 20:15
Solution
please verify the following points:
1. price `price` is specified for the item `productId`,
and how do we know the price of the goods `productPairId` ?
2. if not a secret, tell us why you chose the model which can be no more than two items in the purchase?
3. can the price change frequently and it is necessary to consider the last price?

the query might look like this:
MACTH 
 (p1:Product {productId:'Signal'})<-[:HAS_PRODUCT]-(psc:Purchase)
WITH
 p1, psc
ORDER BY 
 psc.purchasedAt DESC
WITH //get the last value of a given product
 p1, collect(psc)[0].price as lastPrice1 
MACTH
(p1)-[:HAS_CATEGORY]->(c2:Category)<-[:HAS_CATEGORY]-(p2)<-[:HAS_PRODUCT]-(psc:Purchase)
WITH DISTINCT
 p1, lastPrice1, p2, psc
ORDER BY
 psc.purchasedAt DESC
WITH //get the last value of goods of the same categories
 p1, lastPrice1, p2, collect(psc)[0].price as lastPrice2
WHERE //leave cheaper
 lastPrice1 > lastPrice2
MATCH 
(p2)
OPTIONAL MATCH 
(p2)<-[:HAS_PRODUCT|HAS_PAIR_PRODUCT]-(psc:Purchase)
WHERE // consider it payment for the last month, consider the two goods in payment
 psc.purchasedAt > timestamp() - 60*60*24*30*1000
RETURN p2, count(psc) as cnt 
ORDER by cnt DESC
LIMIT 5
1. purchase is denormalized couple of purchases. Rather we had to call purchasePair. Accordingly, the product productId with the price price was in the same order with the product productPairId and there will be several such pairs in a single purchase
2. The price of the product productPairId not important. The main thing that they were paired
3. Prices change because there is inflation, the dollar, and promotions. The latest price of a search is not necessary - it can be discounts. Therefore, the limit - price is not less than X. - Julianne_Muller commented on June 14th 19 at 20:18
Another would be to imagine how is the data structure for this request. - barney.Schroeder90 commented on June 14th 19 at 20:21
I got it. also helped already. Thank you, guys. - Julianne_Muller commented on June 14th 19 at 20:24

Find more questions by tags Neo4j