multiple foreign keys in a table referencing the same field, supported?

[henq]

Hello

I am trying to do the following in Rose::DB::Object for a cryptocurrency trading site.

I have a trades table and each trade has a buyer and a seller. Both the buyer and seller refer to entries in the users table. 

A trade has one seller and one buyer, and an user can have zero, 1 or many sell trades (where she is the seller) and ditto buy trades (where she is the buyer).

So I have two columns that are foreign keys in the trades table: seller_id and buyer_id. 

In SQLite3, I indicate these columns as foreign keys.

In the corresponding perl class, I setup the two relationships, to the same table.

I keep getting errors that the second relationship is not okay, 

"No such column 'buyer_id' in table 'trades' referenced from relationship 'buyer' at /usr/local/share/perl/5.18.2/Rose/DB/Object/Metadata/Relationship/ManyToOne.pm line 255,"

But there is a column buyer_id.

I also made the foreign keys expicit in Rose, as not to rely on the foreign keys in the sqlite-db, but that had no effect.

(I did find a R:D:O example use of multiple fk's that point to the same table, but in that example, the fk's reference each a different column.)

Is there some kind of limitation in referencing 2 times the same primary key from one table?

Or am I staring at an ordinary mistake somewhere, but just don't see it?

 

TIA

PRAGMA foreign_keys=ON;

CREATE TABLE 'trades' (

  id                  INTEGER PRIMARY KEY AUTOINCREMENT, 

  seller_id           INTEGER NOT NULL,   

  buyer_id            INTEGER NULL  ,     

  price    DECIMAL (7,2),     -- price

  FOREIGN KEY(seller_id)        REFERENCES users(id),

  FOREIGN KEY(buyer_id)         REFERENCES users(id),

);

The perl class looks like:

# Trade.pm

package MyApp::Models::Trade;

use base qw(MyApp::DB::Object);

use MyApp::Models::User;

__PACKAGE__->meta->setup (

  table      => 'trades',

  unique_key => ['id'],

  auto       => 1,

  relationships =>

  [

    seller  => 

    {

      class       => 'MyApp::Models::User',

      column_map  => { seller_id => 'id'   },

      type        => 'many to one',

    },

        

    buyer  => 

    {

      class       => 'MyApp::Models::User',

      column_map  => { buyer_id  => 'id'},

      type        => 'many to one',

    },

  ],       

);

# ---  User.pm

package MyApp::Models::User;

use base qw(MyApp::DB::Object);

use MyApp::Models::Trade;

__PACKAGE__->meta->setup (

    table      => 'users',

    unique_key => [ 'id'],

    auto       => 1,

    

    relationships => 

    [

      sell_trades =>

      {

        class        => 'MyApp::Models::Trade',

        column_map   => { id => 'seller_id' },

        type         => 'one to many',

       },

 

      buy_trades =>

      {

        class        => 'MyApp::Models::Trade',

        column_map   => { id => 'buyer_id' },

        type         => 'one to many',

       },

    ],

);

[Randall Sindlinger]

I've used a table with multiple foreign keys very similar to this, and not had any problem.

Just glancing over this, to clarify, is buyer_id defined as NULL and not NOT NULL?  Perhaps that is causing the problem?

CREATE TABLE 'trades' (

  id                  INTEGER PRIMARY KEY AUTOINCREMENT, 

  seller_id           INTEGER NOT NULL,   

  buyer_id            INTEGER NULL  ,     

-Randall

--
Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
---
You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-objec...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[henq]

Randal, pfff... that did the trick.  I declared it as NULL because when the trade is on offer, there is no buyer yet. But apparently I will have to assign a dummy buyer to each new trade. Oh well.  

Thanks for the help!

PS: As SQLite gave no error message, I assume Rose gets confused by the NULL foreign key. Maybe I'll do some experimenting to find out.  

[Dave Howorth]

On 2016-04-10 06:30, henq wrote:
> Randal, pfff... that did the trick. I declared it as NULL because when the
> trade is on offer, there is no buyer yet. But apparently I will have to
> assign a dummy buyer to each new trade. Oh well.

Or change your data model to not conflate quotes and trades :)

> Thanks for the help!
> PS: As SQLite gave no error message, I assume Rose gets confused by the
> NULL foreign key. Maybe I'll do some experimenting to find out.
>
> On Sunday, April 10, 2016 at 7:01:26 AM UTC+2, Randall Sindlinger wrote:
>>
>> I've used a table with multiple foreign keys very similar to this, and
>> not had any problem.
>>

>> Just glancing over this, to clarify, is buyer_id defined as NULL and *not*

>> NOT NULL? Perhaps that is causing the problem?
>>

>> *CREATE TABLE 'trades' (*
>> * id INTEGER PRIMARY KEY AUTOINCREMENT, *
>> * seller_id INTEGER NOT NULL, *
>> * buyer_id INTEGER NULL , *
>>
>> -Randall

[henq]

would be overkill, in this case: no counter-offers , only 'grab offer', by first taker...

but thx for thinking with me ;-)

On Monday, April 11, 2016 at 11:27:42 AM UTC+2, djh wrote:

On 2016-04-10 06:30, henq wrote:

> Randal, pfff... that did the trick.  I declared it as NULL because when the -offeer