Well, I'm making a store with geolocated products in WordPress. The question is that when I use the search engine, I need to make a query that is somewhat difficult because it envolves 3 tables as I said.
I have these tables
- wp_stores. Where do I store the locations of the stores I have
- wp_product_store_relations. Where I keep the relationships between products and stores, it is a relationship one (store) to many (product)
product_id, comes from the
id, from the
wp_posts table, which is the table that is used by default in WordPress to store all types of posts, it is categorized by types: there are blog posts, products, etc...
The question is that I have to make a query to the database using the
wp_posts table, but of course, at the same time as in the search engine I want to sort everything by distance, so I have to find out which store has each product in order to know the store location and order and then order it by distance.
In fact, I'm already doing that in the index, but I'm doing it with 3 queries. Here I want to unify everything in one because WordPress forces me to do it like this (I suppose that is done in that way for optimization tasks).
The main code to get the ids of the products by distance is something like this (this is the index code):
$query = "SELECT *, POW(69.1 * (latitude - $lat), 2) + POW(69.1 * ($lon - longitude) * COS(latitude / 57.3), 2) AS distance FROM wp_stores HAVING distance < POW($distance, 2) ORDER BY distance ASC";
$lon are the coordinates of the gps
$distance is the distance, which is 50 kilometers
This works fine, because from here I would get the stores and then from the stores I would get the products in another query or doing a subquery in order to improve performance (I know that it can be improved...)
The thing is that now I need to make a rather long query and I have done something, but... It has its syntax errors that I can't manage to solve.
SELECT *, (SELECT latitude, longitude FROM wp_stores LEFT JOIN wp_product_store_relations ON wp_stores.store_id = wp_product_store_relations.store_id AND wp_product_store_relations.product_id = t1.ID) as store, POW(69.1 * (store.latitude - 37.3955537), 2) + POW(69.1 * (-2.1352134 - store.longitude) * COS(store.latitude / 57.3), 2) AS distance FROM wp_posts t1 WHERE wp_posts.post_type = "product" HAVING distance < POW(50, 2) ORDER BY distance ASC