Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
function show_query($sale_nid) {
$query = db_select('node', 'n');
$query->fields('n', ['nid', 'uid']);
$field_names = [
'field_sale',
'field_user',
'field_website_bidder_id',
];
foreach ($field_names as $field_name) {
$field = field_info_field($field_name);
$table_name = _field_sql_storage_tablename($field);
$query->innerJoin($table_name, $field_name, "n.nid = $field_name.entity_id");
$query->condition("$field_name.entity_type", 'node');
$query->condition("$field_name.bundle", 'bidder_id');
$column_suffix = $field['type'] == 'entityreference' ? 'target_id' : 'value';
// Explicitly set the alias of the column, so it will match the public
// field name.
$query->addField($field_name, $field_name . '_' . $column_suffix, $field_name);
}
// Add field_floor_bidder_id
$field_names = [
'field_floor_bidder_id',
];
foreach ($field_names as $field_name) {
$field = field_info_field($field_name);
$table_name = _field_sql_storage_tablename($field);
$query->leftJoin($table_name, $field_name, "n.nid = $field_name.entity_id");
// $query->condition("$field_name.entity_type", 'node');
// $query->condition("$field_name.bundle", 'bidder_id');
// Explicitly set the alias of the column, so it will match the public
// field name.
// $labels = "
// SELECT n.nid, GROUP_CONCAT(field_floor_bidder_id.field_floor_bidder_id_value SEPARATOR ',')
// FROM node as n
// LEFT JOIN field_data_field_floor_bidder_id field_floor_bidder_id ON n.nid = field_floor_bidder_id.entity_id
// WHERE n.type = 'bidder_id'";
//
// $query->addExpression($labels, 'labels');
// $query->addExpression('GROUP_CONCAT(field_floor_bidder_id.field_floor_bidder_id_value SEPARATOR \', \')', 'field_floor_bidder_id');
$column_suffix = $field['type'] == 'entityreference' ? 'target_id' : 'value';
$query->addField($field_name, $field_name . '_' . $column_suffix, $field_name);
}
// Joins to the User.
$field_names = [
'field_first_name',
'field_last_name',
];
foreach ($field_names as $field_name) {
$field = field_info_field($field_name);
$table_name = _field_sql_storage_tablename($field);
$query->innerJoin($table_name, $field_name, "field_user.field_user_target_id = $field_name.entity_id");
$query->condition("$field_name.entity_type", 'user');
$column_suffix = $field['type'] == 'entityreference' ? 'target_id' : 'value';
// Explicitly set the alias of the column, so it will match the public
// field name.
$query->addField($field_name, $field_name . '_' . $column_suffix, $field_name);
}
// Join the users table.
$query->innerJoin('users', 'u', 'field_user.field_user_target_id = u.uid');
$query->fields('u', ['name']);
$query
// Active users.
->condition('u.status', TRUE)
// Don't include admin, just in case.
->condition('u.uid', 1, '>')
// Users from given site.
->condition('n.status', NODE_PUBLISHED)
->condition('n.type', 'bidder_id')
// Current Sale.
->condition('field_sale.field_sale_target_id', $sale_nid)
->orderBy('field_website_bidder_id.field_website_bidder_id_value')
// Prevent abuse.
->range(0, 1000);
dpq($query);
$result = $query
->execute()
->fetchAllAssoc('nid');
dpm($result);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.