Performing Left Join in Rhomobile

Question

Is it not possible to perform a left join in Rhomobile?

I have models PriceGroups, PriceLookup which have a 1-many relationship (ie. each PriceGroup and have many PriceLookup records).

I need to do a simple SQL Left Join so I have the required information from the PriceGroups Table

SELECT * FROM PriceLookup 
LEFT JOIN PriceGroups ON PriceLookup.price_group_code=PriceGroups.code

I have added this to the price_lookup model:

  belongs_to :price_group_code, 'PriceGroups'

The following is what I have tried in Rhomobile

PriceLookup.find_by_sql("SELECT *
FROM PriceLookup
LEFT JOIN PriceGroups on PriceLookup.price_group_code=PriceGroups.code")

But I get error:

Error: could not prepare statement: 1; Message: no such table: PriceGroups

I know I can do two selects and join them myself but this is a very crap way of doing it

score 2 · Accepted Answer · edited Sep 19 '13 at 20:23

You need to create the RhoMobile model as FixedSchema, not using the default PropertyBags.

Otherwise you don't have a real table in SQLite but you're using the special objectValues table that is implementing a Key-Value store: http://docs.rhomobile.com/rhodes/rhom#fixed-schema

Example:

dbPT = ::Rho::RHO.get_src_db('PriceLookup')
sql = "SELECT * FROM PriceLookup LEFT JOIN PriceGroups ON PriceLookup.price_group_code=PriceGroups.code"
lines = dbPT.execute_sql(sql)

score 0 · Answer 2 · answered Feb 11 '13 at 10:29

This could happen if the table PriceGroups hasn't been initialized (created) yet. Tables are created when the model class is loaded, if they don't exist. For your case, simply call the model class, this will load it and create the table if necessary.

PriceGroups; #Only for create the table

PriceLookup.find_by_sql("SELECT * FROM PriceLookup LEFT JOIN PriceGroups on PriceLookup.price_group_code=PriceGroups.code")

Performing Left Join in Rhomobile

2 Answers2