[MechDB] ActiveRecord is not created for view without _id column

[imhoff]

If a view is defined in the database that has no column called "_id" no ActiveRecord class is created for this view.

Example:

package com.examle.model
database SampleDB {

    migration {
        create table table_a (
            _id integer primary key autoincrement,
            table_a_value text
        );

        create view view_on_table_a as
            select
                a.table_a_value as view_on_a_value
            from 
                table_a as a;           
    }
}

[fluxtah]

It was actually a conscious decision to not generate active record classes for tables or views that do not have an _id column.

Do you have a case where you need one?

[imhoff]

If you want to use an active record as a convenient way to access your data from a cursor. I have the issue especially for views where no meaningful _id column exists.

Consider the following tables modelling a * to * relationship:

artists
-- _id -- | -- name -- |

albums
-- _id -- | -- name -- |

artist_album_relation
-- artist_id -- | -- album_id -- |

If you now want to create a view, containing the albums with their corresponding artists you would do something like

create view albums_and_artists as
    select
        album.name as album_name,
        artist.name as artist_name
    from albums as album
    left join artist_album_relation as relation
        on album._id = relation.album_id
    left join artists as artist
        on artist._id = relation.artist_id

In this case you can of cause chose one of the two Ids as a _id field but it wouldn't really make sense.

[fluxtah]

Ok, I see your point, as a workaround you could give a dummy id such as:

select
        0 as _id,
        album.name as album_name,
        artist.name as artist_name

If we were to generate ActiveRecord for all then it would mean we would need to change the base ActiveRecord class to remove the id property, as well as some methods that require id such as the static get, etc.

[imhoff]

That's exactly the workaround that I apply. If the behavior is intended and won't be changed, I suggest that some kind of a hint should be added to the documentation. Took me a while to figure out that the missing _id column was the problem.

[fluxtah]

Cool cheers I will update the docs to make sure this is clear