title: How to find data in Sequelize using data in child tables
body: <p>So, I have this query created using Sequelize to find profiles in my database, but I want to be able to find results using the criteria that is stored in different (child) tables / models, not only on the main table.</p>
  
  <p>Here it is a functional extract of my code:</p>
  
  <p><strong>Sequelize model set-up:</strong></p>
  
  <pre><code>const Profile = sequelize.define('profiles',
      { // Database columns:
          profile_id: {
              type: Sequelize.STRING,
              primaryKey: true
          },
          user_name: {
              type: Sequelize.STRING,
              unique: true
          }
      });
  
  const Email = sequelize.define('profiles_emails',
      { // Database columns:
          entry_id: {
              type: Sequelize.INTEGER,
              primaryKey: true,
              autoIncrement: true
          },
          owner_id: {
              type: Sequelize.STRING,
          },
          email_address: {
              type: Sequelize.STRING,
          }
      });
  
  const Phone = sequelize.define('profiles_phones',
      { // Database columns:
          entry_id: {
              type: Sequelize.INTEGER,
              primaryKey: true,
              autoIncrement: true
          },
          owner_id: {
              type: Sequelize.STRING,
          },
          phone_number: {
              type: Sequelize.STRING,
          }
      });
  </code></pre>
  
  <p><strong>Set up associations:</strong></p>
  
  <pre><code>Profile.hasMany(Email,
      { // Options:
          foreignKey: 'owner_id'
      });
  
  Profile.hasMany(Phone,
      { // Options:
          foreignKey: 'owner_id'
      });
  
  Profile.hasOne(Password,
      { // Options:
          foreignKey: 'owner_id'
      });
  </code></pre>
  
  <p><strong>Execution:</strong></p>
  
  <pre><code>// TODO: Finding by email and phone does not work because finding in other tables than the parent table is not programed.
  Profile
      .findAll({
         where: {
             [Op.or]: [
                 { profile_id: '665C70C2A9F9' },
                 { user_name: 'user1' },
                 { email_address: 'user1@mail.com' }, 
                 { phone_number: '554111222' }]
          },
          include: [Phone, Email], // Tables that I want to fetch data from.
      })
      .then(function(data) {
          if (data) { console.log('Number of rows found: ' + data.length, JSON.stringify(data)) }
          else { console.log('No entries found.') }
      })
      .catch(function(data) { console.error('An error occurred while making the query:\n' + data) });
  </code></pre>
  
  <p>The "email_address" and "phone_number" columns are stored in different child tables and the Sequelize associations are already made successfully.</p>
  
  <p><strong>Explained from a practical perspective:</strong> I want to be able to fetch users by phone and e-mail, even though those criteria are in different, associated modules.</p>
  
  <p>Any idea on how to do this? I already tried this without success: <a href="http://docs.sequelizejs.com/manual/tutorial/models-usage.html#top-level-where-with-eagerly-loaded-models" rel="nofollow noreferrer">http://docs.sequelizejs.com/manual/tutorial/models-usage.html#top-level-where-with-eagerly-loaded-models</a></p>
  
  <p><strong>Update according to Vivek Doshi's suggestions:</strong></p>
  
  <p>So here is the <strong>1st suggestion</strong> applied:</p>
  
  <pre><code>Profile
      .findAll({
          where: {
              [Op.or]: [
                  { profile_id: '665C70C2A9F9' },
                  { user_name: 'user1' },
                  { '$profiles_emails.email_address$': 'user1@mail.com' },
                  { '$profiles_phones.phone_number$': '554111222' }
              ],
          },
          include: [Phone, Email], // Tables that I want to fetch data from.
          limit: 5 // Limit of profiles to look for.
      })
      .then(function(data) {
          if (data) { console.log('Number of rows found: ' + data.length, JSON.stringify(data)) }
          else { console.log('No entries found.') }
      })
      .catch(function(data) { console.error('An error occurred while making the query:\n' + data) });
  </code></pre>
  
  <p>Which results in an error:</p>
  
  <pre><code>Executing (default): SELECT `profiles`.*, `profiles_phones`.`entry_id` AS `profiles_phones.entry_id`, `profiles_phones`.`owner_id` AS `profiles_phones.owner_id`, `profiles_phones`.`phone_number` AS `profiles_phones.phone_number`, `profiles_phones`.`verified` AS `profiles_phones.verified`, `profiles_phones`.`modified_time` AS `profiles_phones.modified_time`, `profiles_phones`.`modified_by` AS `profiles_phones.modified_by`, `profiles_emails`.`entry_id` AS `profiles_emails.entry_id`, `profiles_emails`.`owner_id` AS `profiles_emails.owner_id`, `profiles_emails`.`email_address` AS `profiles_emails.email_address`, `profiles_emails`.`verified` AS `profiles_emails.verified`, `profiles_emails`.`modified_time` AS `profiles_emails.modified_time`, `profiles_emails`.`modified_by` AS `profiles_emails.modified_by` FROM (SELECT `profiles`.`profile_id`, `profiles`.`user_name`, `profiles`.`modified_time`, `profiles`.`modified_by` FROM `profiles` AS `profiles` WHERE (`profiles`.`profile_id` = '665C70C2A9F9' OR `profiles`.`user_name` = 'user1' OR `profiles_emails`.`email_address` = 'user1@mail.com' OR `profiles_phones`.`phone_number` = '554111222') LIMIT 5) AS `profiles` LEFT OUTER JOIN `profiles_phones` AS `profiles_phones` ON `profiles`.`profile_id` = `profiles_phones`.`owner_id` LEFT OUTER JOIN `profiles_emails` AS `profiles_emails` ON `profiles`.`profile_id` = `profiles_emails`.`owner_id`;
  
  An error occurred while making the query:
  SequelizeDatabaseError: Unknown column 'profiles_emails.email_address' in 'where clause'
  </code></pre>
  
  <p>And here is the <strong>2nd suggestion</strong> applied:</p>
  
  <pre><code>Profile
      .findAll({
          where: {
              [Op.or]: [
                  { profile_id: '665C70C2A9F9' },
                  { user_name: 'user1' },
              ],
          },
          include: [{
              model : Phone,
              where : { phone_number: '554111222' } ,
              required : false
          }, {
              model : Email,
              where : { email_address: 'user1@mail.com' } ,
              required : false
          }],
          limit: 5 // Limit of profiles to look for.
      })
      .then(function(data) {
          if (data) { console.log('Number of rows found: ' + data.length, JSON.stringify(data)) }
          else { console.log('No entries found.') }
      })
      .catch(function(data) { console.error('An error occurred while making the query:\n' + data) });
  </code></pre>
  
  <p>Which results in:</p>
  
  <pre><code>Executing (default): SELECT `profiles`.*, `profiles_phones`.`entry_id` AS `profiles_phones.entry_id`, `profiles_phones`.`owner_id` AS `profiles_phones.owner_id`, `profiles_phones`.`phone_number` AS `profiles_phones.phone_number`, `profiles_phones`.`verified` AS `profiles_phones.verified`, `profiles_phones`.`modified_time` AS `profiles_phones.modified_time`, `profiles_phones`.`modified_by` AS `profiles_phones.modified_by`, `profiles_emails`.`entry_id` AS `profiles_emails.entry_id`, `profiles_emails`.`owner_id` AS `profiles_emails.owner_id`, `profiles_emails`.`email_address` AS `profiles_emails.email_address`, `profiles_emails`.`verified` AS `profiles_emails.verified`, `profiles_emails`.`modified_time` AS `profiles_emails.modified_time`, `profiles_emails`.`modified_by` AS `profiles_emails.modified_by` FROM (SELECT `profiles`.`profile_id`, `profiles`.`user_name`, `profiles`.`modified_time`, `profiles`.`modified_by` FROM `profiles` AS `profiles` WHERE (`profiles`.`profile_id` = 'user1' OR `profiles`.`user_name` = 'jorge') AND ( SELECT `owner_id` FROM `profiles_phones` AS `profiles_phones` WHERE (`profiles_phones`.`phone_number` = '9' AND `profiles_phones`.`owner_id` = `profiles`.`profile_id`) LIMIT 1 ) IS NOT NULL LIMIT 5) AS `profiles` INNER JOIN `profiles_phones` AS `profiles_phones` ON `profiles`.`profile_id` = `profiles_phones`.`owner_id` AND `profiles_phones`.`phone_number` = '9' LEFT OUTER JOIN `profiles_emails` AS `profiles_emails` ON `profiles`.`profile_id` = `profiles_emails`.`owner_id` AND `profiles_emails`.`email_address` = 'user1@mail.com';
  </code></pre>
  
  <p>This one works and finds rows. But it fetches results associated to the results of the "main" model (this is, the data is found by userid / user_name and then, those results will be accompanied or not depending on wether the other data (email and phone) matched). This is not what I want now, <strong>I want to be able to find a full profile with all its data and with the same structure using the criteria / columns in other tables.</strong></p>