Left Join between tables

General
1

Hey guys, how is it going?

I’m trying to accomplish a simple scenario of ‘user-favorites’ here.
My schema:
|------------------|--------------|----|-------|

Column Type Null Default
//id// int(11) No
car_vendor varchar(20) No
car_model varchar(20) No
car_petro varchar(10) No
car_transmition varchar(10) No
car_hand int(11) No
car_spedometer varchar(7) No
car_engine_vol varchar(4) No
car_category int(11) No
car_post_date date No second table
---------------- ------------ ----
Column Type Null Default
---------------- ------------ ----
//id// int(11) No
username varchar(20) No
favorites_id int(11) No <<< foreign key points to ID on first table

The query I need to run

 SELECT CAR_VENDOR,
    A.CAR_MODEL,
    A.CAR_PETRO,
    A.CAR_TRANSMITION,
    A.CAR_HAND,
    A.CAR_SPEDOMETER,
    A.CAR_ENGINE_VOL,
    A.CAR_CATEGORY,
    A.CAR_POST_DATE,
    B.USERNAME
FROM TABLE_ONE AS A
LEFT JOIN TABLE_TWO AS B ON B.FAVORITES_ID = A.ID
AND B.USERNAME = "actual user username"

Is that possible ?? Maybe using business logic or on Android.

Thanks

2

Hi, Leonardo.

It seems you can solve this issue without LEFT JOIN.
As I understood, you would like to retrieve a user by his name with his favorite car. Suppose you have two tables Users and Cars.
Please do next:

  • create a one-to-one relation between table ‘Users’ (e.g. column ‘favorite_car’) to table ‘Cars’
  • for getting a user with his favorite car do
BackendlessDataQuery query = new BackendlessDataQuery();
QueryOptions queryOptions = new QueryOptions();
String whereClause = "username LIKE '%username%'";
queryOptions.setRelationsDepth( 2 );
query.setQueryOptions( queryOptions );
query.setWhereClause( whereClause );


Backendless.Data.of( BackendlessUser.class ).find( query, 
new AsyncCallback&lt;BackendlessCollection&lt;BackendlessUser&gt;>()
{
 @Override
 public void handleResponse( BackendlessCollection&lt;BackendlessUser&gt; response )
 {
 }
 
 @Override
 public void handleFault( BackendlessFault fault )
 {
 }
}
 );

Regards, Ilya

3

Although I agree, it seems like a terrible approach, since I’d need to use like, which is terrible for performance and a 2 depth search.
Is there other way ?

4

You do not necessarily need LIKE, if you have a concrete username:

String whereClause = "username = 'username'";

Same way you do not necessarily need to load 2 levels of relations, you can just load what you need:

queryOptions.addRelated( "favorite_car" );
5

Hmm I agree with point 1, but i’m still not sure about 2, doing so would load all cars, even those without the favorites ? I mean, the favorites part of the query would return null when the user never favorited it, right ?

6

Right. You’ll get a list of users which name is ‘username’ and each of them will have an object of favorite car or null.

7

Thanks for the quick reply, I’m gonna give it a try.

8

Simply adding to the car table didn’t work

queryOptions.addRelated( "favorite_car" );

The relation is one-sided, favorites has one-to-many to cars.
I’m not sure how to proceed.

Thanks

9

You should add column ‘favorite_car’ to the Users table. The type of this column should be ‘DATA OBJECT RELATIONSHIP’ and related table should be ‘Cars’.
Please, pay attention to documentation about related tables.

10

I thought about it, but it does not make sense to add a column to user, since it’s a one-to-many relation, it does not make it a left join.

My point is, there are a lot of workarounds but not the exact point I asked, right ?

11

Yes.
I just analyzed what you would like to get by using the SQL query from your example and offered the way how it can be solved by Backendless features.

12

Sure Ilya, really appreciate all the help, I’ll see what I can do !

Thanks!