Junction Table Example

[Jeff]

Brand new to orbroker and scala :-) Great job on the library.

Successfully using it for simple queries(movies, actors) but
struggling to figure out how to set up extractors for a junction
table. Any guidance or help would be appreciated.

thanks,
Jeff


Classes
class Movie(var id:Option[Long], var title:String, var rated:String =
"NA", var actors:List[Actor] = Nil)
class Actor(var id:Option[Long], var name:String)

Tables
CREATE TABLE `actor` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `title_UNIQUE` (`name`)
) ;

CREATE TABLE `movie` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) DEFAULT NULL,
`rated` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `title_UNIQUE` (`title`)
) ;

CREATE TABLE `cast` (
`movie_id` bigint(20) NOT NULL,
`actor_id` bigint(20) NOT NULL,
KEY `actors_id_fk` (`actor_id`),
KEY `movie_id_fk` (`movie_id`)
) ;


SQL
SELECT * FROM ACTORS WHERE id IN (SELECT actor_id FROM cast WHERE
movie_id= :movieID);

[Nils]

I notice that your Scala model doesn't quite match your entities. E.g. actor name is nullable, but not represented by an Option[String]. This is a mismatch that should be fixed. I also don't understand why the primary key datatypes don't match (i.e. int(11) in main tables and bigint(20) in the junction table), but that's less of an issue.

So because of the mismatch, I'm going to use getOrElse on the option, just in case it's null. If the mismatch is fixed, you should either keep the Option, or if not nullable, just do the get.

Anyway, assuming the class design is the correct one, try something like this:

object ActorExtractor extends JoinExtractor[Actor] {

val key = Set("id")

def extract(row: Row, join: Join) = new Actor(

row.bigInt("id"),

row.string("name").getOrElse("NULL")

)

}

/**

 * Allow this extractor to be used both with and without joining to cast.

 */

class MovieExtractor(withCast: Boolean) extends JoinExtractor[Movie] {

val key = Set("id")

val actorRenames = Map("id" -> "actorID")

def extract(row: Row, join: Join) = {

val movieID = row.bigInt("id")

val title = row.string("title").getOrElse("NULL")

val rated = row.string("rated").getOrElse("NULL") 

val actors = if (withCast) {

 join.extractSeq(ActorExtractor, actorRenames)

} else {

Seq.empty

}

new Movie(movieID, title, rated, actors) 

}

}

Hope that makes sense. If not, ask some more :-)

[Nils]

Oh, I should mention that the above is based on a query like this:

select

m.id,

m.title,

m.rated,

c.actor_id,

a.name

from movie m

left outer join cast c

on m.id = c.movie_id

join actor a

on c.actor_id = a.id

order by

m.id

[Nils]

Oh, and actor column renames should be: Map("id" -> "actor_id")

[Jeff Thorne]

Thanks for the quick rely Nils.

I have used your suggestion with the following  SQL query:

select * from cast JOIN movie ON movie.id = cast.movie_id JOIN actor ON actor.id = cast.actor_id WHERE movie_id= :movieID

And I get the following error:
Exception in thread "main" org.orbroker.exception.MoreThanOneException: Statement 'selectMovie' returned more than one row

Here is the request from my DAO object:
def find(id:Long):Movie = {
  val movie = broker.readOnly() { session = 

    session.selectOne(Tokens.selectMovie, "movieID"-> id)
  }:Option[Movie] 

  

  return movie.get

 }

I am assuming the problems is with my selectOne call but am not sure how to structure the request returning multiple actors for a movie.

Much Appreciated,

Jeff

[Jeff Thorne]

NIls. Ignore my last email. I just saw your other 2 come it. I will try your suggestions first.

[Jeff Thorne]

Still getting the following error after I made your last 2 changes.

Exception in thread "main" org.orbroker.exception.MoreThanOneException: Statement 'selectMovie' returned more than one row

It must be how I am making the call from the DAO object.

On Wed, Oct 19, 2011 at 6:01 PM, Jeff Thorne <jeff....@gmail.com> wrote:

[Nils Kilden-Pedersen]

On Wed, Oct 19, 2011 at 8:01 PM, Jeff Thorne <jeff....@gmail.com> wrote:

Thanks for the quick rely Nils.

I have used your suggestion with the following  SQL query:

select * from cast JOIN movie ON movie.id = cast.movie_id JOIN actor ON actor.id = cast.actor_id WHERE movie_id= :movieID

And I get the following error:
Exception in thread "main" org.orbroker.exception.MoreThanOneException: Statement 'selectMovie' returned more than one row

I would guess that since you "select *" there's a name clash, since both actor and movie tables use 'id' as their primary key, and you've told the extractor that "id" is the name of the movie key.

[Jeff Thorne]

Morning Nils,

We have this in the extractor:

val actorRenames = Map("id" -> "actor_id")

Does this not avoid the clash?

Also from my DAO I am calling:
session.selectOne(Tokens.selectMovie, "movieID"-> id)

In think selectOne is causing the error. Doesn't it only expect a single row? I am getting more that one result back.

Here is an example of the return of the query;

+----+----------------+-------+----------+--------------+

| id | title          | rated | actor_id | name         |

+----+----------------+-------+----------+--------------+

| 19 | meet joe black | PG    |       20 | Brad Pitt    |

| 19 | meet joe black | PG    |       21 | Clare Forlani |

+----+----------------+-------+----------+--------------+

Thanks,
Jeff

[Nils Kilden-Pedersen]

On Thu, Oct 20, 2011 at 8:28 AM, Jeff Thorne <jeff....@gmail.com> wrote:

In think selectOne is causing the error. Doesn't it only expect a single row? I am getting more that one result back.

Yes, it's interpreting it as you getting more than one movie back.

 

Here is an example of the return of the query;

+----+----------------+-------+----------+--------------+

| id | title          | rated | actor_id | name         |

+----+----------------+-------+----------+--------------+

| 19 | meet joe black | PG    |       20 | Brad Pitt    |

| 19 | meet joe black | PG    |       21 | Clare Forlani |

+----+----------------+-------+----------+--------------+

That cannot be the result of the query you posted. I would expect the result to include these columns, because you selected * (all columns):

movie_id, actor_id, id, title, rated, id, name

However, if you change your select to include only the columns you posted above, I would suspect that it works.

Let me know.

[Jeff Thorne]

Nils, Just got it working.

Thanks for all your help. I had the cast set to false. Great work on ORBroker. Once I get up to speed a little more I'll help contribute some documentation for other noobs like me.

took me over an hour and a post to scala-user to figure out this was a curry function :-)

broker.readOnly() { session =>
  session.selectOne(Tokens.selectMovie, "movieID"-> id)
}:Option[Movie]

Have a good one,
Jeff

[Jeff Thorne]

Nils,

Thanks again for all your help. I am sure I'll be sending other questions you way shortly :-)

I have created a quick blog post on an orBroker and Junction Table example. Hopefully this will help out other noobs and cut down on some of the questions coming your way.

http://jeffthorne.posterous.com/orbroker-junction-table-example

One last thought. For the use of Broker.readOnly what are the advantages of using the curried function here on the client use of the API. Only asking as the syntax is cryptic for new comers to the Scala world.

val movie = broker.readOnly() { session =  
   session.selectOne(Tokens.selectMovie, "movieID"-> id)
 }:Option[Movie]

Cheers,

Jeff

[Nils Kilden-Pedersen]

On Thu, Oct 20, 2011 at 9:51 AM, Jeff Thorne <jeff....@gmail.com> wrote:

One last thought. For the use of Broker.readOnly what are the advantages of using the curried function here on the client use of the API. Only asking as the syntax is cryptic for new comers to the Scala world.

You mean as a function, or particular that it's curried? The former because it makes resource management automatic, there's nothing to remember to close, and the latter because the syntax reads easier.

[Jeff Thorne]

that it is curried. probably that I am new to Scala but I didn't find it easy to read and still trying to understand the benefit of curried functions as opposed to just calling it like

broker.readOnly(param1, param2)

cheers,

JT

[Nils Kilden-Pedersen]

On Thu, Oct 20, 2011 at 12:06 PM, Jeff Thorne <jeff....@gmail.com> wrote:

that it is curried. probably that I am new to Scala but I didn't find it easy to read and still trying to understand the benefit of curried functions as opposed to just calling it like

broker.readOnly(param1, param2)

But then you'd have to define the access code previous to the call, which looks weird (to me):

val block = (session: Queryable) => {

  session.selectOne(...)

}

broker.readOnly()(block) // still curried

broker.readOnly(<what here?>, block) // not curried, but you lose ability default isolation level

broker.readOnly(block) // Isolation level defaulted as second parameter

Overall more typing because you lose type inference, and it just looks less readable than the inline function syntax.