sql-advance.md

sql-advance-proforma.md

transactions_group_by_proforma = Transaction.select('transaction_id as id, count(proforma_id) as proforma_count').group('transaction_id')
transactions = Transaction.where(transaction_id: transactions_group_by_proforma.map(&:id)).where.not(proforma_id: nil).group_by(&:transaction_id)

transactions_group_by_proforma.each do |parent_transaction|
  puts "Root: #{parent_transaction.id}, Proforma: #{parent_transaction.proforma_count}"
  puts "--- Childs ---"
  if transactions[parent_transaction.id.to_i].present?
    transactions[parent_transaction.id.to_i].each.with_index(1) do |child_transaction, index|
      puts "#{index}: #{child_transaction.proforma_no}"
    end
  else
    puts "No childs"
  end
  puts "------"
end

Sample output

Root: 16, Proforma: 0
--- Childs ---
No childs
------
Root: 24, Proforma: 0
--- Childs ---
No childs
------
Root: 1234, Proforma: 1
--- Childs ---
1: 1001-proforma-001
------
Root: 1654, Proforma: 3
--- Childs ---
1: 1001-proforma-001
2: 1001-proforma-001
3: 1001-proforma-001
------
Root: 3241, Proforma: 3
--- Childs ---
1: 1001-proforma-001
2: 1001-proforma-001
3: 1001-proforma-001
------
Root: 9897, Proforma: 1
--- Childs ---
1: 1001-proforma-001
------
Root: 123456, Proforma: 3
--- Childs ---
1: tr-12345123
2: tr-12345123
3: tr-12345123n
------
Root: 1180306, Proforma: 0
--- Childs ---
No childs
------
Root: 1180308, Proforma: 0
--- Childs ---
No childs