25#include <QDomDocument>
28#include <QInputDialog>
38 QWidget *parent, Qt::WindowFlags fl )
40 , mPreviousFieldRow( -1 )
45 connect( btnEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnEqual_clicked );
46 connect( btnLessThan, &QPushButton::clicked,
this, &QgsQueryBuilder::btnLessThan_clicked );
47 connect( btnGreaterThan, &QPushButton::clicked,
this, &QgsQueryBuilder::btnGreaterThan_clicked );
48 connect( btnPct, &QPushButton::clicked,
this, &QgsQueryBuilder::btnPct_clicked );
49 connect( btnIn, &QPushButton::clicked,
this, &QgsQueryBuilder::btnIn_clicked );
50 connect( btnNotIn, &QPushButton::clicked,
this, &QgsQueryBuilder::btnNotIn_clicked );
51 connect( btnLike, &QPushButton::clicked,
this, &QgsQueryBuilder::btnLike_clicked );
52 connect( btnILike, &QPushButton::clicked,
this, &QgsQueryBuilder::btnILike_clicked );
53 connect( lstFields, &QListView::clicked,
this, &QgsQueryBuilder::lstFields_clicked );
54 connect( lstFields, &QListView::doubleClicked,
this, &QgsQueryBuilder::lstFields_doubleClicked );
55 connect( lstValues, &QListView::doubleClicked,
this, &QgsQueryBuilder::lstValues_doubleClicked );
56 connect( btnLessEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnLessEqual_clicked );
57 connect( btnGreaterEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnGreaterEqual_clicked );
58 connect( btnNotEqual, &QPushButton::clicked,
this, &QgsQueryBuilder::btnNotEqual_clicked );
59 connect( btnAnd, &QPushButton::clicked,
this, &QgsQueryBuilder::btnAnd_clicked );
60 connect( btnNot, &QPushButton::clicked,
this, &QgsQueryBuilder::btnNot_clicked );
61 connect( btnOr, &QPushButton::clicked,
this, &QgsQueryBuilder::btnOr_clicked );
62 connect( btnGetAllValues, &QPushButton::clicked,
this, &QgsQueryBuilder::btnGetAllValues_clicked );
63 connect( btnSampleValues, &QPushButton::clicked,
this, &QgsQueryBuilder::btnSampleValues_clicked );
64 connect( buttonBox, &QDialogButtonBox::helpRequested,
this, &QgsQueryBuilder::showHelp );
66 QPushButton *pbn =
new QPushButton( tr(
"&Test" ) );
67 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
70 pbn =
new QPushButton( tr(
"&Clear" ) );
71 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
74 pbn =
new QPushButton( tr(
"&Save…" ) );
75 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
76 pbn->setToolTip( tr(
"Save query to QQF file" ) );
79 pbn =
new QPushButton( tr(
"&Load…" ) );
80 buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
81 pbn->setToolTip( tr(
"Load query from QQF file" ) );
88 layerSubsetStringChanged();
90 lblDataUri->setText( tr(
"Set provider filter on %1" ).arg( layer->
name() ) );
91 mTxtSql->setText( mOrigSubsetString );
93 mFilterLineEdit->setShowSearchIcon(
true );
94 mFilterLineEdit->setPlaceholderText( tr(
"Search…" ) );
95 connect( mFilterLineEdit, &QgsFilterLineEdit::textChanged,
this, &QgsQueryBuilder::onTextChanged );
103 QDialog::showEvent( event );
106void QgsQueryBuilder::populateFields()
109 mTxtSql->setFields( fields );
110 for (
int idx = 0; idx < fields.
count(); ++idx )
118 myItem->setData( idx );
119 myItem->setEditable(
false );
120 mModelFields->insertRow( mModelFields->rowCount(), myItem );
124 setupLstFieldsModel();
127void QgsQueryBuilder::setupLstFieldsModel()
129 lstFields->setModel( mModelFields );
132void QgsQueryBuilder::setupGuiViews()
135 mModelFields =
new QStandardItemModel();
136 mModelValues =
new QStandardItemModel();
137 mProxyValues =
new QSortFilterProxyModel();
138 mProxyValues->setSourceModel( mModelValues );
140 lstFields->setViewMode( QListView::ListMode );
141 lstValues->setViewMode( QListView::ListMode );
142 lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
143 lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
145 lstFields->setUniformItemSizes(
true );
146 lstValues->setUniformItemSizes(
true );
148 lstFields->setAlternatingRowColors(
true );
149 lstValues->setAlternatingRowColors(
true );
150 lstValues->setModel( mProxyValues );
153void QgsQueryBuilder::fillValues(
int idx,
int limit )
156 mModelValues->clear();
159 QList<QVariant> values = qgis::setToList( mLayer->
uniqueValues( idx, limit ) );
160 std::sort( values.begin(), values.end() );
166 const auto constValues = values;
167 for (
const QVariant &var : constValues )
172 else if ( var.type() == QVariant::Date && mLayer->
providerType() == QLatin1String(
"ogr" ) && mLayer->
storageType() == QLatin1String(
"ESRI Shapefile" ) )
173 value = var.toDate().toString( QStringLiteral(
"yyyy/MM/dd" ) );
174 else if ( var.type() == QVariant::List || var.type() == QVariant::StringList )
176 const QVariantList list = var.toList();
177 for (
const QVariant &val : list )
179 if ( !value.isEmpty() )
180 value.append(
", " );
185 value = var.toString();
187 QStandardItem *myItem =
new QStandardItem( value );
188 myItem->setEditable(
false );
189 myItem->setData( var, Qt::UserRole + 1 );
190 mModelValues->insertRow( mModelValues->rowCount(), myItem );
195void QgsQueryBuilder::btnSampleValues_clicked()
197 lstValues->setCursor( Qt::WaitCursor );
199 const QString prevSubsetString = mLayer->
subsetString();
200 if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
202 mIgnoreLayerSubsetStringChangedSignal =
true;
207 fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
212 mIgnoreLayerSubsetStringChangedSignal =
false;
215 lstValues->setCursor( Qt::ArrowCursor );
218void QgsQueryBuilder::btnGetAllValues_clicked()
220 lstValues->setCursor( Qt::WaitCursor );
222 const QString prevSubsetString = mLayer->
subsetString();
223 if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
225 mIgnoreLayerSubsetStringChangedSignal =
true;
230 fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
235 mIgnoreLayerSubsetStringChangedSignal =
false;
238 lstValues->setCursor( Qt::ArrowCursor );
249 const long long featureCount { mLayer->
featureCount() };
251 if ( featureCount < 0 )
253 QMessageBox::warning(
this,
254 tr(
"Query Result" ),
255 tr(
"An error occurred when executing the query, please check the expression syntax." ) );
259 QMessageBox::information(
this,
260 tr(
"Query Result" ),
261 tr(
"The where clause returned %n row(s).",
"returned test rows", featureCount ) );
266 QMessageBox::warning(
this,
267 tr(
"Query Result" ),
268 tr(
"An error occurred when executing the query." )
269 + tr(
"\nThe data provider said:\n%1" ).arg( mLayer->
dataProvider()->
errors().join( QLatin1Char(
'\n' ) ) ) );
274 QMessageBox::warning(
this,
275 tr(
"Query Result" ),
276 tr(
"An error occurred when executing the query." ) );
282 if ( mTxtSql->text() != mOrigSubsetString )
289 QMessageBox::warning(
this,
290 tr(
"Query Result" ),
291 tr(
"An error occurred when executing the query." )
292 + tr(
"\nThe data provider said:\n%1" ).arg( mLayer->
dataProvider()->
errors().join( QLatin1Char(
'\n' ) ) ) );
297 QMessageBox::warning(
this, tr(
"Query Result" ), tr(
"Error in query. The subset string could not be set." ) );
315void QgsQueryBuilder::btnEqual_clicked()
317 mTxtSql->insertText( QStringLiteral(
" = " ) );
321void QgsQueryBuilder::btnLessThan_clicked()
323 mTxtSql->insertText( QStringLiteral(
" < " ) );
327void QgsQueryBuilder::btnGreaterThan_clicked()
329 mTxtSql->insertText( QStringLiteral(
" > " ) );
333void QgsQueryBuilder::btnPct_clicked()
335 mTxtSql->insertText( QStringLiteral(
"%" ) );
339void QgsQueryBuilder::btnIn_clicked()
341 mTxtSql->insertText( QStringLiteral(
" IN " ) );
345void QgsQueryBuilder::btnNotIn_clicked()
347 mTxtSql->insertText( QStringLiteral(
" NOT IN " ) );
351void QgsQueryBuilder::btnLike_clicked()
353 mTxtSql->insertText( QStringLiteral(
" LIKE " ) );
359 return mTxtSql->text();
364 mTxtSql->setText( sqlStatement );
367void QgsQueryBuilder::lstFields_clicked(
const QModelIndex &index )
369 if ( mPreviousFieldRow != index.row() )
371 mPreviousFieldRow = index.row();
373 btnSampleValues->setEnabled(
true );
374 btnGetAllValues->setEnabled(
true );
376 mModelValues->clear();
377 mFilterLineEdit->clear();
381void QgsQueryBuilder::lstFields_doubleClicked(
const QModelIndex &index )
383 mTxtSql->insertText(
'\"' + mLayer->
fields().
at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).
name() +
'\"' );
387void QgsQueryBuilder::lstValues_doubleClicked(
const QModelIndex &index )
389 const QVariant value = index.data( Qt::UserRole + 1 );
391 mTxtSql->insertText( QStringLiteral(
"NULL" ) );
392 else if ( value.type() == QVariant::Date && mLayer->
providerType() == QLatin1String(
"ogr" ) && mLayer->
storageType() == QLatin1String(
"ESRI Shapefile" ) )
393 mTxtSql->insertText(
'\'' + value.toDate().toString( QStringLiteral(
"yyyy/MM/dd" ) ) +
'\'' );
394 else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong || value.type() == QVariant::Bool )
395 mTxtSql->insertText( value.toString() );
397 mTxtSql->insertText(
'\'' + value.toString().replace(
'\'', QLatin1String(
"''" ) ) +
'\'' );
402void QgsQueryBuilder::btnLessEqual_clicked()
404 mTxtSql->insertText( QStringLiteral(
" <= " ) );
408void QgsQueryBuilder::btnGreaterEqual_clicked()
410 mTxtSql->insertText( QStringLiteral(
" >= " ) );
414void QgsQueryBuilder::btnNotEqual_clicked()
416 mTxtSql->insertText( QStringLiteral(
" != " ) );
420void QgsQueryBuilder::btnAnd_clicked()
422 mTxtSql->insertText( QStringLiteral(
" AND " ) );
426void QgsQueryBuilder::btnNot_clicked()
428 mTxtSql->insertText( QStringLiteral(
" NOT " ) );
432void QgsQueryBuilder::btnOr_clicked()
434 mTxtSql->insertText( QStringLiteral(
" OR " ) );
438void QgsQueryBuilder::onTextChanged(
const QString &text )
440 mProxyValues->setFilterCaseSensitivity( Qt::CaseInsensitive );
441 mProxyValues->setFilterWildcard( text );
450void QgsQueryBuilder::btnILike_clicked()
452 mTxtSql->insertText( QStringLiteral(
" ILIKE " ) );
458 lblDataUri->setText( uri );
461void QgsQueryBuilder::showHelp()
463 QgsHelp::openHelp( QStringLiteral(
"working_with_vector/vector_properties.html#query-builder" ) );
475 const QString lastQueryFileDir = s.
value( QStringLiteral(
"/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
477 QString saveFileName = QFileDialog::getSaveFileName(
nullptr, tr(
"Save Query to File" ), lastQueryFileDir, tr(
"Query files (*.qqf *.QQF)" ) );
478 if ( saveFileName.isNull() )
483 if ( !saveFileName.endsWith( QLatin1String(
".qqf" ), Qt::CaseInsensitive ) )
485 saveFileName += QLatin1String(
".qqf" );
488 QFile saveFile( saveFileName );
489 if ( !saveFile.open( QIODevice::WriteOnly | QIODevice::Truncate ) )
491 QMessageBox::critical(
nullptr, tr(
"Save Query to File" ), tr(
"Could not open file for writing." ) );
496 QDomElement queryElem = xmlDoc.createElement( QStringLiteral(
"Query" ) );
497 const QDomText queryTextNode = xmlDoc.createTextNode( subset );
498 queryElem.appendChild( queryTextNode );
499 xmlDoc.appendChild( queryElem );
501 QTextStream fileStream( &saveFile );
502 xmlDoc.save( fileStream, 2 );
504 const QFileInfo fi( saveFile );
505 s.
setValue( QStringLiteral(
"/UI/lastQueryFileDir" ), fi.absolutePath() );
515 mTxtSql->insertText( subset );
522 const QString lastQueryFileDir = s.
value( QStringLiteral(
"/UI/lastQueryFileDir" ), QDir::homePath() ).toString();
524 const QString queryFileName = QFileDialog::getOpenFileName(
nullptr, tr(
"Load Query from File" ), lastQueryFileDir, tr(
"Query files" ) +
" (*.qqf);;" + tr(
"All files" ) +
" (*)" );
525 if ( queryFileName.isNull() )
530 QFile queryFile( queryFileName );
531 if ( !queryFile.open( QIODevice::ReadOnly ) )
533 QMessageBox::critical(
nullptr, tr(
"Load Query from File" ), tr(
"Could not open file for reading." ) );
536 QDomDocument queryDoc;
537 if ( !queryDoc.setContent( &queryFile ) )
539 QMessageBox::critical(
nullptr, tr(
"Load Query from File" ), tr(
"File is not a valid xml document." ) );
543 const QDomElement queryElem = queryDoc.firstChildElement( QStringLiteral(
"Query" ) );
544 if ( queryElem.isNull() )
546 QMessageBox::critical(
nullptr, tr(
"Load Query from File" ), tr(
"File is not a valid query document." ) );
550 subset = queryElem.text();
554void QgsQueryBuilder::layerSubsetStringChanged()
556 if ( mIgnoreLayerSubsetStringChangedSignal )
558 mUseUnfilteredLayer->setDisabled( mLayer->
subsetString().isEmpty() );
static QString nullRepresentation()
This string is used to represent the value NULL throughout QGIS.
QString displayNameWithAlias() const
Returns the name to use when displaying this field and adds the alias in parenthesis if it is defined...
Container of fields for a vector layer.
@ OriginProvider
Field comes from the underlying data provider of the vector layer (originIndex = index in provider's ...
int count() const
Returns number of items.
FieldOrigin fieldOrigin(int fieldIdx) const
Returns the field's origin (value from an enumeration).
QgsField at(int i) const
Returns the field at particular index (must be in range 0..N-1).
static void enableAutoGeometryRestore(QWidget *widget, const QString &key=QString())
Register the widget to allow its position to be automatically saved and restored when open and closed...
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
QString providerType() const
Returns the provider type (provider key) for this layer.
static bool loadQueryFromFile(QString &subset)
Load query from the XML file.
void loadQuery()
Load query from the XML file.
void saveQuery()
Save query to the XML file.
void setDatasourceDescription(const QString &uri)
void setSql(const QString &sqlStatement)
Set the sql statement to display in the dialog.
virtual void test()
The default implementation tests that the constructed sql statement to see if the vector layer data p...
static bool saveQueryToFile(const QString &subset)
Save query to the XML file.
void showEvent(QShowEvent *event) override
QgsQueryBuilder(QgsVectorLayer *layer, QWidget *parent=nullptr, Qt::WindowFlags fl=QgsGuiUtils::ModalDialogFlags)
This constructor is used when the query builder is called from the vector layer properties dialog.
QString sql() const
Returns the sql statement entered in the dialog.
This class is a composition of two QSettings instances:
QVariant value(const QString &key, const QVariant &defaultValue=QVariant(), Section section=NoSection) const
Returns the value for setting key.
void setValue(const QString &key, const QVariant &value, QgsSettings::Section section=QgsSettings::NoSection)
Sets the value of setting key to value.
Interface for a dialog that can edit subset strings.
static bool isNull(const QVariant &variant)
Returns true if the specified variant should be considered a NULL value.
void clearErrors()
Clear recorded errors.
QStringList errors() const
Gets recorded errors.
bool hasErrors() const
Provider has errors to report.
Represents a vector layer which manages a vector based data sets.
long long featureCount(const QString &legendKey) const
Number of features rendered with specified legend key.
QgsFields fields() const FINAL
Returns the list of fields of this layer.
void subsetStringChanged()
Emitted when the layer's subset string has changed.
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
QgsVectorDataProvider * dataProvider() FINAL
Returns the layer's data provider, it may be nullptr.
virtual bool setSubsetString(const QString &subset)
Sets the string (typically sql) used to define a subset of the layer.
QSet< QVariant > uniqueValues(int fieldIndex, int limit=-1) const FINAL
Calculates a list of unique values contained within an attribute in the layer.
#define QgsDebugMsgLevel(str, level)