0

I had a ActiveJDBC model called Job, and defined some static attributes like title, salary, workplace and so on.

public class Job extends Model {

  public String getTitle() {
    return getString("title");
  }

  public void setTitle(String title) {
    setString("title", title);
  }

  public Integer getSalary() {
    return getInteger("salary");
  }

  public void setSalary(Integer salary) {
    setInteger("salary", salary);
  } 

  public String getWorkplace() {
    return getString("workplace");
  }

  public void setWorkplace(String workplace) {
    setString("workplace", workplace);
  }  
}

Now I want to find jobs based on geometry distance by below sql:

String sql = "select *, ST_distance(...) as distance from jobs... order by distance asc";
LazyList<Job> jobs = Job.findBySql(sql);

How can I read the virtual attribute distance from Job model?

I have tried to add distance column in jobs table, and it reported error ERROR: ORDER BY "distance" is ambiguous

Jack Tang
  • 59
  • 5
  • Please, provide a relevant code of your model . Also, `ERROR: ORDER BY "distance" is ambiguous` is obviously an SQL error – ipolevoy Sep 23 '21 at 15:58
  • @ipolevoy I updated the model. And yes, it was the sql error because I added `distance` column in order to get the value of distance in result set. My question is how can I get the virtual attribute `distance` which is not defined in table. – Jack Tang Sep 24 '21 at 04:27

1 Answers1

1

Not sure what you mean by a "virtual" attribute, but ActiveJDBC models are just Java classes, so you can add whatever you want to them. JavaLite will not be handling them for you though. It is your responsibility to add appropriate setters and getters and maintain their state as if this was a regular Java class.

So, you want this wrapped into a model:

String sql = "select *, ST_distance(...) as distance from jobs... order by distance asc";
LazyList<Job> jobs = Base.findAll(sql);

you are on the right path. Please, read the JavaDoc for this method carefully:

http://javalite.github.io/2.4-j8/org/javalite/activejdbc/Model.html#findBySQL-java.lang.String-java.lang.Object...-

Especially focus on this part: "Ensure that the query returns all columns associated with this model, so that the resulting models could hydrate themselves properly.".

The "*" in your query takes care of the first part, and the second part will be ignored by a model automatically but will participate in the selection: " Returned columns that are not part of this model will be ignored, but can be used for clauses like above."

So, all you have to do is to write a method like this:


public class Job{

  public List<Map> getNearJobs(){

     String sql = "select *, ST_distance(. ? . ? .) as distance from jobs... order by distance asc";
     return Base.findAll(sql, getLogitude(), getLatitude());
  }

}

this way, you will construct a query that will bring (and order) the right maps with their attributes filled appropriately.

So, you will get a list of Maps, not Jobs, but it will include all the data you need. You can use this approach with some code acrobatics to create new models from a list of maps (Model.fromMap()) and separately inject your "distance" attribute in each model. However, I'm personally against this because then a model is not a model since a model is mapping to a table.

ipolevoy
  • 5,432
  • 2
  • 31
  • 46
  • the "virtual" attribute I mean which is the attribute is calculated from the other attributes. In my example, the attribute `distance` is calculated from `workplace` long/lat coordinate and `home` coordinate. And you are right, I can get the `distance` attribute value by some model code. In another way, postgresql provides `ST_distance` function, and I want to calculate the value when executing the sql and want to get it from model. – Jack Tang Sep 26 '21 at 02:23
  • call the method `getNearJobs()` a distance and consider it your "virtual" attribute. Have you tried the above? – ipolevoy Sep 27 '21 at 07:28
  • I had tried. `getNearJobs` returns list of jobs without `distance` attribute, but I need the value. – Jack Tang Sep 28 '21 at 07:07
  • as I mentioned in the above answer: " Returned columns that are not part of this model will be ignored, but can be used for clauses like above.". This means that if you need a distance as a part of your result, you cannot use models. I will update the answer. – ipolevoy Sep 28 '21 at 14:53
  • I updated the answer based on this. – ipolevoy Sep 28 '21 at 15:27