Hello REST Frameworks : Why NO ‘JOINS’ ?

XMYSQL provides an API to do JOINs on MySql.

MVC/Rest API Frameworks providing basic CRUD operations are a good way to get started and build basic apps from a framework. However, basic APIs are rarely enough in real world apps. Boundaries of Rest-API frameworks can be pushed by having ‘GROUP BY’ operations as discussed in previous post [ Hello Rest Framework, Why no ‘GROUPS’ ? ].


This post explores on how Xmysql* turns ANY JOIN OPERATION on MySql to an API.

(* : Xmysql: One command to serve REST APIs for any MySql database)


Xjoin : Simple example : Join two tables

SELECT *
FROM productlines as pl
JOIN products as pr
ON pl.productline = pr.productline

Equivalent xjoin API:

/api/xjoin?_join=pl.productlines,_j,pr.products&_on1=(pl.productline,eq,pr.productline)

Syntax of xjoin :

/api/xjoin?_join=alias.table1,_j,alias.table2&_on1=(expression1)
_join : takes a list of table names where each table is separated by type of join. (_j, _lj, _rj) is (join, left join, right join) respectively.
_on1  : indicates condition used in first join
_on2 : indicates condition used in second join and so on ..

Xjoin : Join three tables example

SELECT *
FROM productlines as pl
JOIN products as pr
ON pl.productline = pr.productline
JOIN orderdetails as ord
ON pr.productcode = ord.productcode

Xjoin equivalent

/api/xjoin?_join=pl.productlines,_j,pr.products,_j,ord.orderDetails&_on1=(pl.productline,eq,pr.productline)&_on2=(pr.productcode,eq,ord.productcode)

Note _on2 will be the condition used for second join with orderDetails table

Joins with columns selected, pagination, where condition, sorting, group by, having can be invoked using Xmysql list api syntax shown as below.

/api/xjoin?_join=pl.productlines,_j,pr.products&_on1=(pl.productline,eq,pr.productline)&_fields=pl.productline,pr.productName&_p=2&_size=15&_where=(pr.price,gt,100)

Above examples demonstrate on how joins can be invoked as simple APIs a MySql database using Xmysql.


Improvements can be envisioned in terms of nested response of complex JOINs and getting rid of conditional expression to rely on foreign keys and likes.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.