#1000 suggestion
Nate Wiger

DataMapper serialization and property/value mapping strange behavior

Reported by Nate Wiger | August 3rd, 2009 @ 01:17 PM

I've been evaluating DataMapper for a good week now, and I felt compelled to open this because it appears DM has an approach for mapping values that leaves lots of possibilities for application bugs.

Getting records back normally works great. But I also have to do hand-tweaked SQL in many cases because there are lots of uses for DB-level aggregation functions, joins, DB views, etc. In these cases, from a user perspective, DM appears to behave really strangely.

If I say:

@results = Highscore.find_by_sql <<EndSQL
   select h.score, r.rank, h.username, h.updated_at
     from highscores h
    inner join rankings r on r.highscore_id = h.id
    order by h.score desc
 EndSQL

Then do:

@results.to_json

I'll get a big structure with mismatched properties/values, and null for missing values:

{"highscores":[{ "id": 445455, "game_id": 1, "username": null, "score": null, .... }

In this situation, DM has mapped the properties (id, game_id, username, score, ...) to values (score, rank, username, updated_at) from the query. This example is using the do_mysql driver.

Furthermore, since username and updated_at are not integers (which is what the DM properties are, but not the values from the query), the serialization appears to convert them to null as well.

I did alot of googling and found about the +:properties+ flag to +find_by_sql+. However, that falls apart in a use-case where you use a SQL function that doesn't map to a property:

@results = Highscore.find_by_sql <<EndSQL
   select h.*, rank(over (order by h.avg_score desc)) as rank
     from highscores h
    inner join rankings r on r.highscore_id = h.id
    order by rank
 EndSQL

The query executes ok, but this causes an exception:

@results.first.rank  # No such property "rank"

And, the +.to_json+ serialization again maps the column names/values in the order they were received, not based on the name of the column. (And "rank" is omitted)

There may be the temptation to close this as "works as designed" (if that is the case), but the current DM behavior makes me very hesitant to use it, because it introduces lots of opportunities for developer accidents. It is possible I'm doing something wrong, but I couldn't find anything in the DM wiki or google to see how to get DM to do what I need. Thoughts?

Thanks,
Nate

Comments and changes to this ticket

  • Dan Kubb (dkubb)

    Dan Kubb (dkubb) September 10th, 2009 @ 04:38 PM

    • State changed from “new” to “suggestion”

    Nate, you are completely right. Model#find_by_sql has always been sort of ghetto, but we're working on fixing that. I should have rolled it back when it was first committed, and pushed it into a plugin, where it could get more focused attention. Having an RDBMS-only feature part of a library that tries hard to be storage agnostic was a mistake.

    I've since pushed it into dm-ar-finders within the dm-more repository. A 0.10 release will be available next Tuesday, and includes some find_by_sql fixes from people who use it regularly.

    However, in this specific case, you haven't said if the Array of objects you're getting back need to be actual instances of Highscore (with all the methods and behavior), or if an Array of Struct objects would be enough.

    In my case, when I do queries like the ones you've posted, I'm usually trying to get aggregate information from the DB for reporting, presentation or API output. In those case you'd be fine with using DO directly, eg:

    results = repository.adapter.query(<<-SQL
          SELECT h.score, r.rank, h.username, h.updated_at
            FROM highscores h
      INNER JOIN rankings r ON r.highscore_id = h.id
        ORDER BY h.score DESC
    SQL
    

    This will return an Array of Struct objects, with the methods in the struct object matching the column/alias names in the SELECT part.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Pages