Writing a query using orelse (OR) in chicagoboss

[laxmikantG]

trafficstars

I think, there is no such option in boss_db API to find a query in the format

SELECT * FROM TableName WHERE  table.key1 = "XXXX1" OR   table.key2 = "XXXX2"

I searched a lot and finally raised a Stackoverflow Query! And came up to a decision by suggestion to raise an issue at github.

Thanks

Stackoverflow Query.

[choptastic]

Indeed, this is not currently possible, and something that will need to be fixed before 1.0.

[davidw]

I pretty much reach for SQL whenever I have anything remotely complicated to fetch.

[laxmikantG]

Hello @davidw and @choptastic : Thanks for replying!!! So, now is there the only way to achieve that, is just writing two queries?

           X1 = boss_db:find(table, {key1, equals, "XXXX1"}),
           X2 = boss_db:find(table, {key2, equals, "XXXX2"}),
           X = X1 ++ X2.

Or is there also anything else? Please let me know. Thank you

[davidw]

Erlang does not have operator overloading, so you can't do something like that.

The best way to do a complex query is boss_db:find_by_sql or boss_db:execute, so that you can just use plain old SQL and deal with the results it returns.

[laxmikantG]

@davidw : Thanks for the reply!. But I am not agree that erlang does not have operator overloading. See the first line in the List Handling! It works pretty well, Please have a look at following queries. I have run it in boss_db shell :

(my_project@laxmikantG)1>X1 = boss_db:find(forms_data, [{name, 'equals', "prvl_bussiness_ritht_0000_h200401"}]).
[{forms_data,"forms_data-53b3fe7e2bf3e5112c00001f",
                 {{2014,7,2},{12,43,42}},
                 {{2014,7,2},{12,43,42}},
                 [],undefined,"prvl_bussiness_ritht_0000_h200401",{},
                 {'NUM_05_parent',25},
                 undefined},
     {forms_data,"forms_data-53d0b0cd2bf3e5768b00000e",
                 {{2014,7,24},{7,7,57}},
                 {{2014,7,24},{7,7,57}},
                 [],undefined,"prvl_bussiness_ritht_0000_h200401",{},
                 {'NUM_05_parent',25},
                 undefined}]

(my_project@laxmikantG)2>X2 = boss_db:find(forms_data, [{id, 'equals', "forms_data-53b3fe7e2bf3e5112c00001f"}]).
   [{forms_data,"forms_data-53b3fe7e2bf3e5112c00001f",
                 {{2014,7,2},{12,43,42}},
                 {{2014,7,2},{12,43,42}},
                 [],undefined,"prvl_bussiness_ritht_0000_h200401",{},
                 {'NUM_05_parent',25},
                 undefined}]

(my_project@laxmikantG)3> X1++X2.
            [{forms_data,"forms_data-53b3fe7e2bf3e5112c00001f",
                 {{2014,7,2},{12,43,42}},
                 {{2014,7,2},{12,43,42}},
                 [],undefined,"prvl_bussiness_ritht_0000_h200401",{},
                 {'NUM_05_parent',25},
                 undefined},
     {forms_data,"forms_data-53d0b0cd2bf3e5768b00000e",
                 {{2014,7,24},{7,7,57}},
                 {{2014,7,24},{7,7,57}},
                 [],undefined,"prvl_bussiness_ritht_0000_h200401",{},
                 {'NUM_05_parent',25},
                 undefined},
     {forms_data,"forms_data-53b3fe7e2bf3e5112c00001f",
                 {{2014,7,2},{12,43,42}},
                 {{2014,7,2},{12,43,42}},
                 [],undefined,"prvl_bussiness_ritht_0000_h200401",{},
                 {'NUM_05_parent',25},
                 undefined}]

Please let me know, If I understood something wrong. Thank you

[davidw]

Hi,

++ concatenates lists, nothing more, nothing less. It's not overloaded.

You are not writing a query with "OR", but two queries and then merging the results. That should work, but it's different than writing one query.

What I meant earlier was that you can't do something like Rails' Arel where you write the queries and then it merges them before running them. Of course you can do as many queries as you want and then add up the results, but that might not be very efficient.

[laxmikantG]

@davidw : Got you now! Thanks :+1:

[7i11]

you can use some like this:

Var = case boss_db:find(heir,[{flag1, 'equals', true}]) of 
             Data -> Data;
             _ -> case boss_db:find(heir,[{flag2, 'equals', true}]) of 
                        Data -> Data;
                        _ -> []
                     end 
          end.

[choptastic]

That would only work if you're looking to find a single record that matches the critieria (like find_first). Otherwise, consider data consisting of

Row1: flag1=true, flag2=true Row2: flag1=true, flag2=false Row3: flag1=false, flag2=true

The provided query would only return Row1 and Row2, but the proper return would be all three rows.

On Tue, Oct 28, 2014 at 4:45 PM, Orlando Jimenez [email protected] wrote:

you can use some like this: Var = case boss_db:find(heir,[{flag1, 'equals', true}]) of Data -> Data; [] -> case boss_db:find(heir,[{flag2, 'equals', true}]) of Data -> Data; [] -> "no data" end end.

— Reply to this email directly or view it on GitHub https://github.com/ChicagoBoss/boss_db/issues/185#issuecomment-60837227.

Jesse Gumm Owner, Sigma Star Systems 414.940.4866 || sigma-star.com || @jessegumm