How do I group by and order by count in Rails through a join table?
Date : March 29 2020, 07:55 AM
may help you . How do I translate this SQL into Rails? , Try: Posts.first.tags.select("tags.tag, count(1) as tag_count").group('tags.tag').order('tag_count desc')
|
Rails 4 OR query with join table using group and having
Date : March 29 2020, 07:55 AM
wish helps you ActiveRecord cannot do or but Arel can. The amazing scuttle.io will solve your problem: Position.select(Position.arel_table[Arel.star]).where(
Position.arel_table[:user_id].eq(4).or(
Position.arel_table[:active].eq(0).or(Position.arel_table[:deadline].lt('2015-01-21')).or(Cv.arel_table[:accepted].eq(1))
)
).joins(
Position.arel_table.join(Cv.arel_table).on(
Cv.arel_table[:position_id].eq(Position.arel_table[:id])
).join_sources
).group(:id)
|
JOIN Many table while GROUP BY and COUNT the GROUP BY row in MySQL
Tag : php , By : Valentine
Date : March 29 2020, 07:55 AM
hop of those help? I think this problem is easier to solve with PHP. Use your query and fetch your data into an associative array. I should look like this: $dbData = [
['id_lpkon' => 1, 'id_lpseb' => 1, 'id_lprek' => 1, 'id_lptl' => 1],
['id_lpkon' => 1, 'id_lpseb' => 1, 'id_lprek' => 1, 'id_lptl' => 2],
['id_lpkon' => 1, 'id_lpseb' => 1, 'id_lprek' => 2, 'id_lptl' => null],
['id_lpkon' => 1, 'id_lpseb' => 2, 'id_lprek' => null, 'id_lptl' => null,],
['id_lpkon' => 2, 'id_lpseb' => null, 'id_lprek' => null, 'id_lptl' => null,]
];
$columns = ['id_lpkon', 'id_lpseb', 'id_lprek', 'id_lptl'];
$counts = [];
foreach ($columns as $column) {
$counts[$column] = [];
}
foreach ($dbData as $row) {
foreach ($row as $column => $value) {
if ($value === null) {
continue;
} elseif (isset($counts[$column][$value])) {
$counts[$column][$value]++;
} else {
$counts[$column][$value] = 1;
}
}
}
$counts = [
'id_lpkon' => [1 => 4, 2 => 1],
'id_lpseb' => [1 => 3, 2 => 1],
'id_lprek' => [1 => 2, 2 => 1],
'id_lptl' => [1 => 1, 2 => 1],
];
foreach ($dbData as $rn => $row) {
foreach ($row as $column => $value) {
$countColumn = $column . '_count';
if ($value === null) {
$dbData[$rn][$countColumn] = null;
} else {
$dbData[$rn][$countColumn] = $counts[$column][$value];
}
}
}
|
LINQ: How can I Join to child table using data from group join?
Date : March 29 2020, 07:55 AM
wish helps you Assuming the TitleSearchResult has List types for Old_Titles and Transactions, you can do: var ans = From t In query3.ToList() _
Select New TitleSearchResult() With {
.Title = t.title,
.Pc_Parcel = t.parcel,
.Transactions = t.transactions.Select(Function(t1) t1.trans).ToList(),
.Old_Titles = t.transactions.Select(Function(t1) t1.title_old).ToList()
}
|
Retireve max/min row with left join trick for a group, when group is on another table
Tag : mysql , By : user98832
Date : March 29 2020, 07:55 AM
Does that help What you need to do to make this work is to LEFT JOIN the JOIN of the two tables to each other: SELECT p1.*, m1.*, m2.*
FROM (messparent p1 JOIN messages m1 ON p1.fk_mess = m1.id)
LEFT JOIN
(messparent p2 JOIN messages m2 ON p2.fk_mess = m2.id)
ON m2.when > m1.when AND p2.grp = p1.grp
WHERE m2.id IS NULL
grp fk_mess id when id when
1 3 3 2000-08-16 (null) (null)
2 6 6 2000-08-19 (null) (null)
|
Boards Message :
Related QUESTIONS :