Join() One-to-many relationship

tom@pdp

I have two tables: users with id (1,2) and view_users with user_id (1). When I use Join() the result that I get appears as a regular left join relationship and not as a join with one-to-many relationship.

$editor = Editor::inst( $db, 'users', 'id' )
->fields(Field::inst( 'users.id' ), Field::inst( 'users.username' ))
->join(
Join::inst( 'users_view', 'array' )
->join( 'id', 'user_id' )
->fields( Field::inst( 'user_id' ) ->validator( 'Validate::required' ))
);

I expect to get one record with user_id=1 only, but instead I get both of them (without second user_id):

{
    "data": [
        {
            "DT_RowId": "row_1",
            "users": {
                "id": "1",
                "username": "john"
            },
            "users_view": [
                {
                    "user_id": "1"
                }
            ]
        },
        {
            "DT_RowId": "row_2",
            "users": {
                "id": "2",
                "username": "steve"
            },
            "users_view": []
        }
    ],
    "options": []
}

I can use something like this instead:

$editor->where( 'users_view.user_id', $_SESSION['user'] )
->leftJoin( 'users_view', 'users_view.user_id', '=', 'users.id' );

However, this trick won't work in all other cases that I'll need to implement.

How do I specify in the code, using Join() alone that users_view, without user_id, shouldn't appear at all?

Thanks.

allan

Hi Tom,

Thanks for your question. The Editor instance is setup to use the table users as the master table, which you note has two records in it - 1 and 2. This is why Editor is giving back information about both rows.

Your where statement looks perfect to limit it to a single row. I don't think the leftJoin statement should be required.

I have a feeling I haven't answered this fully though - could you give me an SQL dump of your two db tables?

Thanks,
Allan

tom@pdp

Hi Allan,

Thank you for your quick reply. Here is the dump:

DROP TABLE IF EXISTS users;
CREATE TABLE IF NOT EXISTS users (
  id int(11) NOT NULL AUTO_INCREMENT,
  username varchar(50) NOT NULL,
  password varchar(118) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO users (id, username, password) VALUES
    (1, 'john', 'blah'),
    (2, 'steve', 'blah-blah');
    
DROP TABLE IF EXISTS users_view; 
   
CREATE TABLE IF NOT EXISTS users_view (
  id int(11) NOT NULL AUTO_INCREMENT,
  user_id int(11) NOT NULL,
  users_id int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

INSERT INTO users_view (id, user_id, users_id) VALUES
    (1, 1, 1);

The result of Join() from the first post:

{
    "data":[
        {
            "DT_RowId":"row_1",
            "users":{
                "id":"1",
                "username":"john"
            },
            "users_view":[
                {
                    "user_id":"1"
                }
            ]
        },
        {
            "DT_RowId":"row_2",
            "users":{
                "id":"2",
                "username":"steve"
            },
            "users_view":[]
        }
    ],
    "options":[]
}

The query that I'm trying to make:

select * from users join users_view on users_view.user_id=users.id

Tried different options. Still no luck.

Thanks in advance

tom@pdp

$editor = Editor::inst( $db, 'users_view', 'user_id' )
            ->fields(
                Field::inst( 'users_view.id' ),
                Field::inst( 'users_view.user_id' ))
            ->join(
                Join::inst( 'users', 'array' )
                    ->join( 'user_id', 'id' )
                    ->fields(              
            Field::inst( 'id' )
            ->validator( 'Validate::required' )
                        )
            );

users_view as a master table worked in a proper way. Therefore, I understand that join() behaves as a regular LEFT JOIN, and not as JOIN or INNER JOIN The same switching between users and users_view will work with leftJoin() as well. It is the same as I will run SELECT * FROM users_view LEFT JOIN users ON (users_view.user_id = users.id) instead SELECT * FROM users LEFT JOIN users_view ON (users_view.user_id = users.id) WHERE users_view.user_id = $_SESSION['user']. It would be nice to know, if there is an option to use JOIN, INNER JOIN, and RIGHT JOIN as simple as I can use leftJoin()? Because if I'll be using join() with the users, I won't be able to use any conditional statement in the middle of the join operation. Or, can I add more fields to the users, after I close join() parentheses? It might help with the issue somehow, if a regular JOIN is not possible.

allan

Hi,

Thanks for the extra information - based on the SQL, the Join() statement you've got and the resulting output is actually exactly what I would expect at the moment - i.e. each row in the parent table is shown, and it has joined information. So yes, this is implicitly a left join (one-to-many, rather than one-to-one though).

Do you want to show one record in the DataTable for every record (for a user) in the users_view table? If so, use the users_view table in the main Editor instance initialisation and then use leftJoin() to add whatever additional information from the left joined table.

At this time, Editor only supports left joins in its built in methods.

Allan

tom@pdp

Hi Allan,

Thank you for the answer.

My mistake was that I thought that the Join() behaves as a regular JOIN. I didn't expect that the Join() will start showing all the records at once. It was a bit confusing.

I am not sure that something will work with the query I will need to implement by the end, but it was a very valuable information for the future.

I will need to use something like SELECT * FROM users WHERE id = ANY (SELECT users_id FROM users_view WHERE user_id=1);, if I won't find something better. I am afraid that this type of queries, including queries with INNER JOIN, still is not supported by the Editor. Now, I'll need to figure out how to save the data entered by users, while I cannot use the Editor to show them the data.

The only solution that I found for right now to show the data is:

$data = $db->sql( 'SELECT * FROM users WHERE id = ANY (SELECT users_id FROM users_view WHERE user_id=$_SESSION['id']);')->fetchAll();
echo json_encode( array(
      'data' => $data
   ) );