ViewModel - left join on same table multiple times

[MFlisar]

I have a model that has multiple references in another model (1:1 relations). My ModelView attempt looks like following:

@ViewModelSpec(className="MediaView", viewName="mediaView")
public class MediaViewModelSpec
{
    @ViewQuery
    public static final Query QUERY = Query.select()
            .from(Media.TABLE)

            // Following two queries belong together, so FolderSorting must have this folders id as reference!
            .leftJoin(Folder.TABLE, Media.FK_FOLDER.eq(Folder.ID))
            .leftJoin(FolderSorting.TABLE, Folder.FK_SORTING.eq(FolderSorting.ID))

            // Following two queries belong together again
            .leftJoin(Folder.TABLE, Media.FK_FOLDER_DATE.eq(Folder.ID))
            .leftJoin(FolderSorting.TABLE, Folder.FK_SORTING.eq(FolderSorting.ID))

            // Following two queries belong together again
            .leftJoin(Folder.TABLE, Media.FK_FOLDER_LOCATION.eq(Folder.ID))
            .leftJoin(FolderSorting.TABLE, Folder.FK_SORTING.eq(FolderSorting.ID))

            // Following two queries belong together again
            .leftJoin(Folder.TABLE, Media.FK_FOLDER_TAG.eq(Folder.ID))
            .leftJoin(FolderSorting.TABLE, Folder.FK_SORTING.eq(FolderSorting.ID));
}

Explanation is, that each Media belongs to 4 types of folders and each of this folder can have it's own sorting. I want to use the ModelView for this ombined query to query all data at once.

Do I have to use my own aliases so that this type of view works? Or is there some build in solution for this?

Additionally, merging on one and the same table multiple times seems to make problems when I want to use mapToModel. Can I still somehow use it?

[sbosley]

You'll probably need aliases, yes. SquiDB by design doesn't do much magic with its SQL builders, it's a WYSIWYG situation. You can alias tables using as(), and then get the table aliased version of a property using qualifyField:

Table folder1 = Folder.TABLE.as("folder1");
LongProperty folderId1 = folder1.qualifyField(Folder.ID);

We have test cases and a test model spec illustrating the concept you're trying to achieve -- see ThingJoinSpec.

[MFlisar]

I missed the qualifyField function...

So following should work:

@ViewModelSpec(className="MediaView", viewName="mediaView")
public class MediaViewModelSpec
{
    public static final Table TBL_FOLDER = Folder.TABLE.as("F1");
    public static final Property.LongProperty TBL_FOLDER_ID = TBL_FOLDER.qualifyField(Folder.ID);

    public static final Table TBL_FOLDER_DATE = Folder.TABLE.as("F2");
    public static final Property.LongProperty TBL_FOLDER_DATE_ID = TBL_FOLDER_DATE.qualifyField(Folder.ID);

    @ViewQuery
    public static final Query QUERY = Query.select()
            .from(Media.TABLE)

            // Following two queries belong together, so FolderSorting must have this folders id as reference!
            .leftJoin(TBL_FOLDER, Media.FK_FOLDER.eq(TBL_FOLDER_ID))
            .leftJoin(FolderSorting.TABLE, Folder.FK_SORTING.eq(TBL_FOLDER_ID))

            // Following two queries belong together again
            .leftJoin(TBL_FOLDER_DATE, Media.FK_FOLDER_DATE.eq(TBL_FOLDER_DATE_ID))
            .leftJoin(FolderSorting.TABLE, Folder.FK_SORTING.eq(TBL_FOLDER_DATE_ID));

}

And then I can do following:

MediaView mediaView = ...;
Folder folder = mediaView.mapToModel(new Folder(), MediaViewModelSpec.TBL_FOLDER);
Folder folderDate = mediaView.mapToModel(new Folder(), MediaViewModelSpec.TBL_FOLDER_DATE);

I think this will work, so I will close that issue. Thanks

[sbosley]

Yep, that's how it's done! Let us know if you run into any other issues with it.

[sbosley]

Edit: it's possible you'll need to alias the joins on FolderSorting.TABLE as well, and I think your query might mean to say folderSortingAlias.qualifyField(FolderSorting.FK_SORTING).eq(TBL_FOLDER_ID), but I think you have the tools you need now to set up those joins the way you want.

[MFlisar]

yes, you're right, of course they have to be aliases as well... Thanks.

Btw, you may be able to answer following question as well: I have thousands of medias that are linked to a few folders, each folder is linked to a sorting (and some other foreign key property). When I make such a complex query with so many joins (I need a few more than I have in the example above) and most of the time the foreign properties are the same objects, would it make a difference if I query each table seperately and then set the foreign properties in java? Via binary searches and so?

I want to avoid that the database is querying the same foreign property over and over again. I think, sql internal caching solves the problem for me, but I'm not sure. Do you know that?

[sbosley]

In my experience, 99% of the time doing things in a single query is faster than multiple queries. It's just better to delegate more of the work to native SQLite (which is quite fast) than to do the same work in Java. SQLite's query optimizer and page cache are pretty smart, so I think in this scenario they'll probably work fine for your case. The SQL may be more complex when you do things all in a single query, but it's very often faster, especially if you've taken the time to optimize the SQL and make sure all the necessary indexes are set up.

If you feel like things are slow when they should be fast, the first place to look is your indexes! Note that SQLite may not index foreign key columns: see https://www.sqlite.org/foreignkeys.html#fk_indexes

[MFlisar]

Just for others - something to consider:

I think, there is one thing that you have to consider, memory. Having thousands of rows that are all linked to a few objects means, that if you use SQL directly, every linked object is a own object. I this case, you may consider linking foreign key objects in java instead of querying them directly via SQL. In my case, I'll do that.

Question about views

As far as I see, I MUST define each field, that my subquery should select and use an empty select in the query definitions. Am I able to add all fields of a sub table at once? I tried adding an array, but this is not added to the Property array of the view...

Example

@ViewModelSpec(isSubquery = true)
public class FolderViewModelSpec
{
    @ViewQuery
    public static final Query QUERY = Query.select().from(Test.TABLE);

    public static final Property.LongProperty TEST_FIELD1 = Test.TABLE.qualifyField(Test.ROWID);
    public static final Property.LongProperty TEST_FIELD2 = Test.TABLE.qualifyField(Test.FIELD1);
    // ... and so on... list of all fields of the test table
    // this is hard doing it by hand...

    // THIS DOES NOT WORK, those fields are NOT added to the subquery's select
    public static final Field[] TEST_FIELDS = Test.TABLE.qualifyFields(Test.PROPERTIES);

}

I then want to do following:

Test folder = modelView.mapToModel(new Test (), Test.TABLE);

[sbosley]

You're right, of course, about memory usage. SquiDB itself doesn't particularly care how you decide to read things into Java objects -- whatever works best for your app is good :)

And yes, you have to list each field you want included in the view. The annotation processing API can only give you access to values that are compile time constants, but things like property objects or the contents of the properties array of a table are runtime constants only. There's no way to extract them from the array at compile time or to know what each of their types are, which would be needed to generate the individual property declarations for them in the generated view model class. From the perspective of the code generator, the only thing that exists at compile time is the field declaration public static final Field[] TEST_FIELDS; as though no value were given to it. So, they have to each be listed one by one.

[MFlisar]

Can't you change the public static final Field[] to a public static final List<Field> and then do something like following in the annotation processor:

public static final List<Field> fields = new ArrayList<Field>()
{
    {
        // add all defined fields AND arrays of fields here
        add(TEST_FIELD1);
        addAll(Arrays.asList(TEST_FIELDS));
    }
};

This could then respect following in the ModelView specs:

// one row per table must be defined only
// an array of all qualified fields of a particular table
public static final Field[] TEST_FIELDS = Test.TABLE.qualifyFields(Test.PROPERTIES);

public static final Field TEST_FIELD1 = Test.TABLE.qualifyField(Test.FIELD1);

[sbosley]

I see what you're saying, you're suggesting building up the lists of properties at runtime (we'd still have to convert them to arrays when the class is loaded for backwards compatibility reasons but that's not much of an issue). It's an interesting idea, but it comes with a couple major drawbacks:

1) We still wouldn't be able to generate individual property declarations for any fields included via an array, and by extension we wouldn't be able to generate getters/setters for them either. So the model would be unusable on its own: you would ALWAYS have to use mapToModel to extract data from it. 2) You wouldn't be able to construct any queries with WHERE clauses on the view, since no individual properties would be declared in the model.

For the things we use views for, I think both of these restrictions would be major problems, especially the second one. Do you have any follow up thoughts though?

[MFlisar]

If it is not possible to do something like following in an annotation processor

for (int i = 0; i < TEST_FIELDS.size(); i++)
{
    // do something with every value, like generating the property for it
}

then I don't see any sense in adding this with the drawbacks you mention...

[sbosley]

Yeah, not possible unfortunately. The contents of an array or list like that aren't constant at compile time, so from the perspective of the annotation processor they basically don't exist. The fields are represented to the processor as a VariableElement, but you can see from the javadoc here that the only way to get the value of a VariableElement is if it is a constant -- and only primitive values or String constants qualify as compile-time constants.