Search code examples
rhomobilerhodes

Performing Left Join in Rhomobile

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

Solution

  • 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)