KitsuneTech-com / Velox-Server

Platform-neutral database communication framework
3 stars 1 forks source link

Add join capability #68

Open skylardarkfox opened 1 month ago

skylardarkfox commented 1 month ago

The most SQL-like implementation of this would probably involve adding a public join() method on the Model class, taking three arguments: the first being a constant representing the type of join (possibly additive to account for INNER/OUTER variants), the second being the Model to be joined to the calling Model, and the third being an array of two or three elements representing each column to be joined, with an optional operator specifying the comparison to be used for the join ("=" would be default, but a "<>" anti-join could also be supported); the return value would be a Model representing the joined dataset. This would allow for a chain construction that looks a little something like:

$model1->join(LEFT_JOIN,$model2,["primaryKey","=","foreignKey"])->join(INNER_JOIN,$model3,["primaryKey","=","foreignKey"])...

which is reasonably close to the SQL syntax

... FROM model1 LEFT JOIN model2 ON model1.primaryKey = model2.foreignKey INNER JOIN model3 ON model1.primaryKey = model3.foreignKey

The actual analog would be a little more like

... FROM (model1 LEFT JOIN model2 ON model1.primaryKey = model2.foreignKey) AS joined1 INNER JOIN model3 ON joined1.primaryKey = model3.foreignKey

given that subsequent Models would be joined on the aggregate of previous joins, but the distinction at least in this fundamental case is immaterial. Use of the filter() method could also allow a behavior somewhat similar to subqueries, but this would be a bit less intuitive to SQL users if done as part of a method chain.

skylardarkfox commented 1 month ago

Starting to lay out the Model->join() method. Efficiency will be important here, as the amount of operations performed during a join could potentially be massive depending on the datasets and the type of join.