JSON generation with nested objects

Question

PHP

There are two related tables in the database. let's say posts and users:

posts
postid
title
post
date
userid

users
userid
username
usermail We

want to generate such a structure when requested at once, so that after converting to JSON it takes the following form:

{
postid: 453
title: "The blog post"
post: : "Very important blog post"
date: 2013-08-20
user: {
    userid: 34
    username: "Vasya"
    usermail: "[email protected]"
    }
}

Is there a silver bullet or do I need to select posts first, and then include user data with additional queries or otherwise process the selection with JOIN?

Answer 1

You will have to bind flat data to your structure one way or another. It's just that you have a choice, either to write it yourself (a normal loop and mapping fields to an array) or use some orm that will do this for you.

Answer 2

Something does not suit you,
SELECT ... FROM posts p JOIN users u ON u.userid = p.userid
or maybe I did not understand the question?

Answer 3

No, that won't work.
But you can easily do this by splitting the dot-separated field names into subarrays

SELECT postid, user.userid
FROM posts post
JOIN users user ON user.userid = post.userid

Answer 4

There are too few details to answer the question, because there are a lot of answers - for example, use an ORM, say, if you write a web service in .NET, you can use the Entity Framework or LINQ to SQL:

public IList<Post> GetPosts(Guid userId)
{
    using (var context = new DbContext())
    {
        return (from p in context.Posts
            where p.User.Id == userId
            select p).ToList();
    }
}

//...

public class DbContext: DataContext
{
    public Table<Post> Posts { return GetTable<Post>(); }
    public Table<User> Users { return GetTable<User>(); }
    public DbContext(string connectionString):base(connectionString) { }
}

[Table]
public class Post
{
    [Column(IsPrimaryKey = true)]
    public int Id { get; set; }
    [Column]private int _userId;
    private EntityRef<User> _user;
    [Association(Storage = "_user", IsForeignKey = true, ThisKey = "Id")]
    public User User
    {
        get { return _user.Entity; }
        set { _user.Entity = value;_userId = value.Id; }
    }
// ...
}

[Table]
public class User
{
    [Column(IsPrimaryKey = true)]
    public int Id { get; set; }
    
    [Association(OtherKey = "_userId")]private EntitySet<Post> _posts;
// ...
}

Answer 5

In addition to Fesor:
You didn't specify the language, so the bike is in PHP

// описание таблиц
$tableMap = array();

// $postsFields и $usersFields - это описание таблиц - в каком точно порядке
//   поля
$tableMap['posts'] = array( // описание таблицы `posts`
  'fields' => array('postid', 'title', 'post', 'date', 'userid'), // перечисление колонок
  'primary' => 'postid', // primary ключ, для упрощения - несоставной
  'relations' => array( // внешние ключи
    'fk_userid' => array('userid', 'users', 'userid'), // ключ на таблицу `users` по полю `userid`
  ),
);

$tableMap['users'] = array( // описание таблицы `users`
  'fields' => array('userid', 'username', 'usermail'), // перечисление колонок
  'primary' => 'userid', // primary ключ, для упрощения - несоставной
  'relations' => array( // внешние ключи
  ),
);

// $posts и $users - это два хранилища сущностей
$posts = array();
$users = array();

// $resultSet = это имитация результата выборки по запросу
// select
//   p.postid, p.title, p.post, p.date, p.userid,
//   u.userid, u.username, u.usermail
// from posts p
// join users u on p.userid = u.userid
// -- все колонки в этом запросе обязаны следовать в том же порядке, что и в описании таблиц $postsFields и $usersFields

$resulSet = array(
  array(1,'post1_title','post1_content', '2013-10-16', 1, 1, 'login1', '[email protected]'),
  array(2,'post2_title','post2_content', '2013-10-16', 1, 1, 'login1', '[email protected]'),
  array(3,'post3_title','post3_content', '2013-10-16', 2, 2, 'login2', '[email protected]'),
  array(4,'post4_title','post4_content', '2013-10-16', 3, 3, 'login3', '[email protected]'),
  array(5,'post5_title','post5_content', '2013-10-16', 3, 3, 'login3', '[email protected]'),
  array(6,'post6_title','post6_content', '2013-10-16', 4, 4, 'login4', '[email protected]'),
);

// теперь - перебор результатов выборки, составление сущностей с учётом повторения
foreach($resulSet as $row) {
  reset($row);

  // выбор из строки результата всех значений, относящихся к `posts`
  $post = array();
  foreach($tableMap['posts']['fields'] as $key) {
    $post[$key] = current($row);
    next($row);
  }
  // сохранение сущности в хранилище, если ещё не было сохранено
  $pk = $post[$tableMap['posts']['primary']];
  if (!isset($posts[$pk])) {
    $posts[$pk] = $post;
  }

  // выбор из строки результата всех значений, относящихся к `users`
  $user = array();
  foreach($tableMap['users']['fields'] as $key) {
    $user[$key] = current($row);
    next($row);
  }
  // сохранение сущности в хранилище, если ещё не было сохранено
  $pk = $user[$tableMap['users']['primary']];
  if (!isset($users[$pk])) {
    $users[$pk] = $user;
  }
}

// последнее - составление массива с нужной структурой
$collection = array();
$index = 0;
foreach($posts as $post) {
  $collection[$index] = $post;

  // здесь надо воспользоваться $tableMap['posts']['relations']['fk_userid'], но мне лень
  $fkUser = $post['userid'];
  //echo '$fkUser=',$fkUser,PHP_EOL;
  $collection[$index]['user'] = $users[$fkUser];

  $index++;
}

var_export($collection);

Answer 6

And this is how it would look in ORM Propel (PHP)

<?php
$propelQuery = PostsQuery::create()
  ->joinWith('users') // join c включением джойненой таблицы в SELECT ...
;
$propelCollection = $propelQuery->find();

$result = array(); $index = 0;
foreach($propelCollection as $post) {
  $result[$index] = $post->toArray();
  $result[$index]['user'] = $post->getUserss()->toArray();
  $index++;
}

var_export($result);

Answer 7

If you do not fence bicycles, then this is usually done using an ORM. Those. you describe the correspondence of PHP classes to database entities, describe the relationships between entities, then say "Select posts for me along with user". In response, you get ready-made objects of the Post class, for which, for example, the user property is also an object of the User class. After all this, you will only have to do json_encode, or with some kind of preprocessing, according to the situation.
And if without ORM, then the only way out is to independently link the results of queries from tables. In this case, yalinqo can help, but from experience you are better off with the ORM path.