Querys and foreign keys
Federico Vidueiro
I need some help with querys and froreign key.
I have the classes:
public class Photo {
@DatabaseField(generatedId = true)
int id;
@DatabaseField
String description;
@DatabaseField(canBeNull = true, foreign = true)
Album album;
public Photo(){
//
}
}
public class Album {
@DatabaseField(generatedId = true)
int id;
@DatabaseField
String nombre;
Album() {
// needed by ormlite
}
public Album (String nombre){
this.nombre = nombre;
}
@Override
public String toString() {
return nombre;
}
}
the album is the foreign key in photo class.
I need to build a query to get all the photos from given album, something like this:
public List<Photo> getPhotos(Album album){
QueryBuilder <Photo, Integer> queryBuilder = getHelper().getPhotoDao().queryBuilder();
queryBuilder.where().eq("album", album); // Here is the problem
return getHelper().query(queryBuilder.prepare());
}
Thanks in advance,
Federico
Kevin Galligan
issue, and I believe the error you got was the column wasn't in the
db? I'm in crisis mode with my apps right now, so i didn't take any
time to figure it out. I just found the name of the column in the db
and queried that directly.
Most likely, the column is actually called "album_id". So, the line
would look like...
queryBuilder.where().eq("album_id", album.id);
The query *should* understand foreign objects, I think, but there may
be some weirdness involved. In any case, something to discuss. Use
workaround for today.
Its also possible I missed something in the config myself.
-Kevin
Federico Vidueiro
However it would be great if the framework could handle this.
Kevin Galligan
will have a reference to the album, although the reference will only
have the id value (you manually have to refresh it).
But I hear you.
-Kevin
Gray Watson
> @DatabaseField(canBeNull = true, foreign = true)
> Album album;
>
> public List<Photo> getPhotos(Album album){
> QueryBuilder <Photo, Integer> queryBuilder = getHelper().getPhotoDao().queryBuilder();
> queryBuilder.where().eq("album", album); // Here is the problem
> return getHelper().query(queryBuilder.prepare());
> }
Your query is fine except the name of the field is not "album" -- it is "album_id". It seemed wrong to take the field name when we weren't storing the field in the table. The recommended way to do this is:
public class Photo {
public final static String ALBUM_FIELD_NAME = "album";
...
@DatabaseField(canBeNull = true, foreign = true, columnName = ALBUM_FIELD_NAME)
Album album;
}
then:
queryBuilder.where().eq(Photo.ALBUM_FIELD_NAME, album);
I think this is covered in the manual but I will add additional information about id column names. I could also see if the field is unknown then it will look for the field name instead of the column name -- but I might spit a warning message out.
gray
Gray Watson
java.lang.IllegalArgumentException: You should use columnName 'album_id' for table foo instead of fieldName 'album'
Hope this helps. I'd rather not do automatic conversions or anything like that.
gray
Federico Vidueiro
Adam Wong
surgeons = surgeonManager.getSurgeons(0, "lastName", true, "id", "firstName", "lastName", "address1");
QueryBuilder<Surgeon, Integer> builder = surgeonDao.queryBuilder();
if (!StringUtil.isEmpty(columns[0])) {
builder.selectColumns(columns); //The error is thrown here
}
if (orderByColumnName != null) {
builder.orderBy(orderByColumnName, ascending);
}
if (maxRows > 0) {
builder.limit(maxRows);
}
return surgeonDao.query(builder.prepare());
}
And the stack trace:
Gray
Greetings! This was the only place google could find a reference to the error, "java.lang.IllegalArgumentException: You should use columnName 'album_id' for table foo instead of fieldName 'album'" which I'm getting intermittently. Sometimes my android app throws the error, sometimes it does not- I can't even reproduce the error consistently. . Here some code:
03-28 12:59:23.740: E/AndroidRuntime(2833): Caused by: java.lang.IllegalArgumentException: You should use columnName 'id' for table Surgeon instead of fieldName 'id'
Gray Watson
> 03-28 12:59:23.740: E/AndroidRuntime(2833): Caused by: java.lang.IllegalArgumentException: You should use columnName 'id' for table Surgeon instead of fieldName 'id'
Wait, what? Cancel that. I don't understand this at all. You are searching for field 'id'. That you get to this point means that it did not match the column names in the map.
Any chance this is a thread race condition? Could the table be in the process of being initialized when this happened?
gray