Ormlite 2 references to the same table. Join issue

1

Hi,
I have address1 and address2, I want to be able to join and bring the entire relation of each address.
Sometimes I need one address but sometimes I need both.
For some reason, I’m not getting any data in the address object.

  1. why is it empty?
  2. how can I distinguish between the joins?
    Thanks

https://gistlyn.com/?collection=991db51e44674ad01d3d318b24cf0934&gist=3e96376eda7bf239ffcb4b16957fff2a

var frm = db.From<User>().Join<Address>();
var rowsC = db.Select<User>(frm);

public class User
{
    public long Id { get; set; }
    [Index]
    public string Name { get; set; }
    public DateTime CreatedDate { get; set; }
    
    [References(typeof(Address))]
    public long? Address1Id {get;set;}
    [Reference]
    public Address Address1 {get;set;}
    
    [References(typeof(Address))]
    public long? Address2Id {get;set;}
    [Reference]
    public Address Address2 {get;set;}
    
    public override string ToString() => Name;
    
}
2

Please refer to the example in the docs, you always need to use LoadSelect to load references, which have an optional include parameter to specify which references to load.

3

LoadSelect will bring both. Can select only address1?

4

If you don’t want the predefined behavior you’d need to select the main table without references in addition to a custom select to select the reference you want.

5

  1. If LoadSelete brings all the relations, what’s the point in using join? anyway, it will bring all.

  2. Can you please explain what are you referring to? the example that I posted is the same as the docs.

  1. Maybe some more context will help.
    I have a page and I want to present 100 users in a list, the User entity has 15 references. the list should present only 8 references.
    I want to do it in one query, so I thought use Join to do that, but when I use LoadSelect, either way, it will load all.
    How can I run one query with left joins and only 8 specific references?

Thanks

6

LoadSelect loads an entity with references, that’s specifically what the API does, the entity references on your model is so it can be used by Load* APIs, the APIs do let you specify which references to include, but it’s not done with a single query, it requires an additional query per reference type, but if you don’t want the Load* APIs behavior you wouldn’t use it.

If you wanted to use a single custom query with joins instead you can use TableAlias to reference the same table multiple times but you’d then need to use a custom query like SelectMulti to capture the custom resultset of your query.

7

I think selectmulti is what I’m after.
Just to be sure. the following query return 11 rows, it is all done in one query?

  var from = db.From<ResearchEntity>().LeftJoin<OrgEntity>();
  var data = db.SelectMulti<ResearchEntity, OrgEntity>(from);

Thanks!

8

Yes you can view the generated SQL using a BeforeExecFilter, e.g:

OrmLiteUtils.PrintSql();