Join Tables

For relational database, a search usually requires multiple table joins. You can achieve this by using

{
...
  "current_tables" : [
    {"name": "sales",    "alias": "s"},
    {"name": "customer", "alias": "c", "type": "INNER", "using": "customer_id"},
    {"name": "product",  "alias": "p", "type": "INNER", "using": "product_id"},
    {"name": "coupon",   "alias": "n", "type": "LEFT", "on": "p_product_id=n.product_id"}
  ],
  "topics_hash" : {
    "s.sales_id": "sales_id",
    "s.customer_id": "customer_id",
    "c.firstname" : "firstname",
    "c.lastname" : "lastname",
    "p.title" : "title",
    "p.price" : "price",
    "n.discount" : "discount"
  },
  ...
}

Step 1: Add current_tables to component.json

This is a list of joint tables which are represented as objects, having the following key-values:

• name: the table name
• alias: optional, the alias of the table
• type: one of join types – INNER or LEFT
• using: to use USING clause in joining the table
• on: to use ON clause in joining the table

The first item in current_tables is for the main table, so it has only name and alias.

Step 2: Define topics_hash

By default, the resultant fields will use their table names (or table alias) as prefixes. You can assign labels to these cumbersome fields, by topics_hash.

Example

The above current_tables and topics_hash will result in the follow SQL statement:

SELECT s.sales_id AS sales_id, s.customer_id AS customer_id,
  c.firstname AS firstname, c.lastname AS lastname, 
  p.title AS title, p.price AS price, n.discount AS discount
FROM sales s 
INNER JOIN customer c USING (customer_id)
INNER JOIN product p USING (product_id)
LEFT JOIN coupon n ON (p.product_id=n.product_id)

The advantage to use this JOIN solution over an overridden topics is that you can continuously use the built-in fks, pagination and trigger without any extra programming.