void AutoDJFeature::constructCrateChildModel() { // Create a crate table-model with a list of crates that have been added // to the auto-DJ queue (and are visible). QSqlTableModel crateListTableModel(this, m_pTrackCollection->getDatabase()); crateListTableModel.setTable(CRATE_TABLE); crateListTableModel.setSort(crateListTableModel.fieldIndex(CRATETABLE_NAME), Qt::AscendingOrder); crateListTableModel.setFilter(CRATETABLE_AUTODJ_SOURCE + " = 1 AND " + CRATETABLE_SHOW + " = 1"); crateListTableModel.select(); while (crateListTableModel.canFetchMore()) { crateListTableModel.fetchMore(); } QSqlRecord tableModelRecord = crateListTableModel.record(); int nameColumn = tableModelRecord.indexOf(CRATETABLE_NAME); int idColumn = tableModelRecord.indexOf(CRATETABLE_ID); // Create a tree-item for each auto-DJ crate. for (int row = 0; row < crateListTableModel.rowCount(); ++row) { int id = crateListTableModel.data( crateListTableModel.index(row, idColumn)).toInt(); QString name = crateListTableModel.data( crateListTableModel.index(row, nameColumn)).toString(); m_crateList.append(qMakePair(id, name)); // Create the TreeItem for this crate. TreeItem* item = new TreeItem(name, name, this, m_pCratesTreeItem); m_pCratesTreeItem->appendChild(item); } }
void CrateFeature::buildCrateList() { m_crateList.clear(); QString queryString = QString( "CREATE TEMPORARY VIEW IF NOT EXISTS CratesCountsDurations " "AS SELECT " " crates.id as id, " " crates.name as name, " " COUNT(library.id) as count, " " SUM(library.duration) as durationSeconds " "FROM crates " "LEFT JOIN crate_tracks ON crate_tracks.crate_id = crates.id " "LEFT JOIN library ON crate_tracks.track_id = library.id " "WHERE show = 1 " "GROUP BY crates.id;"); QSqlQuery query(m_pTrackCollection->getDatabase()); if (!query.exec(queryString)) { LOG_FAILED_QUERY(query); } QSqlTableModel crateListTableModel(this, m_pTrackCollection->getDatabase()); crateListTableModel.setTable("CratesCountsDurations"); crateListTableModel.setSort(crateListTableModel.fieldIndex("name"), Qt::AscendingOrder); crateListTableModel.select(); while (crateListTableModel.canFetchMore()) { crateListTableModel.fetchMore(); } QSqlRecord record = crateListTableModel.record(); int nameColumn = record.indexOf("name"); int idColumn = record.indexOf("id"); int countColumn = record.indexOf("count"); int durationColumn = record.indexOf("durationSeconds"); for (int row = 0; row < crateListTableModel.rowCount(); ++row) { int id = crateListTableModel.data( crateListTableModel.index(row, idColumn)).toInt(); QString name = crateListTableModel.data( crateListTableModel.index(row, nameColumn)).toString(); int count = crateListTableModel.data( crateListTableModel.index(row, countColumn)).toInt(); int duration = crateListTableModel.data( crateListTableModel.index(row, durationColumn)).toInt(); m_crateList.append(qMakePair(id, QString("%1 (%2) %3") .arg(name, QString::number(count), Time::formatSeconds(duration, false)))); } }