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",
    "" : "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.



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