QGIS API Documentation  3.13.0-Master (788156190c)
qgsquerybuilder.cpp
Go to the documentation of this file.
1 /***************************************************************************
2  qgsquerybuilder.cpp - Query Builder
3  --------------------------------------
4  Date : 2004-11-19
5  Copyright : (C) 2004 by Gary E.Sherman
6  Email : sherman at mrcc.com
7  ***************************************************************************
8  * *
9  * This program is free software; you can redistribute it and/or modify *
10  * it under the terms of the GNU General Public License as published by *
11  * the Free Software Foundation; either version 2 of the License, or *
12  * (at your option) any later version. *
13  * *
14  ***************************************************************************/
15 #include "qgsquerybuilder.h"
16 #include "qgslogger.h"
17 #include "qgsproject.h"
18 #include "qgssettings.h"
19 #include "qgsvectorlayer.h"
20 #include "qgsvectordataprovider.h"
21 #include "qgsapplication.h"
22 #include "qgshelp.h"
23 #include "qgsgui.h"
24 
25 #include <QListView>
26 #include <QMessageBox>
27 #include <QRegExp>
28 #include <QPushButton>
29 
30 // constructor used when the query builder must make its own
31 // connection to the database
33  QWidget *parent, Qt::WindowFlags fl )
34  : QDialog( parent, fl )
35  , mPreviousFieldRow( -1 )
36  , mLayer( layer )
37 {
38  setupUi( this );
40  connect( btnEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnEqual_clicked );
41  connect( btnLessThan, &QPushButton::clicked, this, &QgsQueryBuilder::btnLessThan_clicked );
42  connect( btnGreaterThan, &QPushButton::clicked, this, &QgsQueryBuilder::btnGreaterThan_clicked );
43  connect( btnPct, &QPushButton::clicked, this, &QgsQueryBuilder::btnPct_clicked );
44  connect( btnIn, &QPushButton::clicked, this, &QgsQueryBuilder::btnIn_clicked );
45  connect( btnNotIn, &QPushButton::clicked, this, &QgsQueryBuilder::btnNotIn_clicked );
46  connect( btnLike, &QPushButton::clicked, this, &QgsQueryBuilder::btnLike_clicked );
47  connect( btnILike, &QPushButton::clicked, this, &QgsQueryBuilder::btnILike_clicked );
48  connect( lstFields, &QListView::clicked, this, &QgsQueryBuilder::lstFields_clicked );
49  connect( lstFields, &QListView::doubleClicked, this, &QgsQueryBuilder::lstFields_doubleClicked );
50  connect( lstValues, &QListView::doubleClicked, this, &QgsQueryBuilder::lstValues_doubleClicked );
51  connect( btnLessEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnLessEqual_clicked );
52  connect( btnGreaterEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnGreaterEqual_clicked );
53  connect( btnNotEqual, &QPushButton::clicked, this, &QgsQueryBuilder::btnNotEqual_clicked );
54  connect( btnAnd, &QPushButton::clicked, this, &QgsQueryBuilder::btnAnd_clicked );
55  connect( btnNot, &QPushButton::clicked, this, &QgsQueryBuilder::btnNot_clicked );
56  connect( btnOr, &QPushButton::clicked, this, &QgsQueryBuilder::btnOr_clicked );
57  connect( btnGetAllValues, &QPushButton::clicked, this, &QgsQueryBuilder::btnGetAllValues_clicked );
58  connect( btnSampleValues, &QPushButton::clicked, this, &QgsQueryBuilder::btnSampleValues_clicked );
59  connect( buttonBox, &QDialogButtonBox::helpRequested, this, &QgsQueryBuilder::showHelp );
60 
61  QPushButton *pbn = new QPushButton( tr( "&Test" ) );
62  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
63  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::test );
64 
65  pbn = new QPushButton( tr( "&Clear" ) );
66  buttonBox->addButton( pbn, QDialogButtonBox::ActionRole );
67  connect( pbn, &QAbstractButton::clicked, this, &QgsQueryBuilder::clear );
68 
69  setupGuiViews();
70 
71  mOrigSubsetString = layer->subsetString();
72 
73  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
74 
75  lblDataUri->setText( tr( "Set provider filter on %1" ).arg( layer->name() ) );
76  txtSQL->setText( mOrigSubsetString );
77 
78  mFilterLineEdit->setShowSearchIcon( true );
79  mFilterLineEdit->setPlaceholderText( tr( "Search…" ) );
80  connect( mFilterLineEdit, &QgsFilterLineEdit::textChanged, this, &QgsQueryBuilder::onTextChanged );
81 
82  populateFields();
83 }
84 
85 void QgsQueryBuilder::showEvent( QShowEvent *event )
86 {
87  txtSQL->setFocus();
88  QDialog::showEvent( event );
89 }
90 
91 void QgsQueryBuilder::populateFields()
92 {
93  const QgsFields &fields = mLayer->fields();
94  for ( int idx = 0; idx < fields.count(); ++idx )
95  {
96  if ( fields.fieldOrigin( idx ) != QgsFields::OriginProvider )
97  {
98  // only consider native fields
99  continue;
100  }
101  QStandardItem *myItem = new QStandardItem( fields.at( idx ).displayNameWithAlias() );
102  myItem->setData( idx );
103  myItem->setEditable( false );
104  mModelFields->insertRow( mModelFields->rowCount(), myItem );
105  }
106 
107  // All fields get ... setup
108  setupLstFieldsModel();
109 }
110 
111 void QgsQueryBuilder::setupLstFieldsModel()
112 {
113  lstFields->setModel( mModelFields );
114 }
115 
116 void QgsQueryBuilder::setupGuiViews()
117 {
118  //Initialize the models
119  mModelFields = new QStandardItemModel();
120  mModelValues = new QStandardItemModel();
121  mProxyValues = new QSortFilterProxyModel();
122  mProxyValues->setSourceModel( mModelValues );
123  // Modes
124  lstFields->setViewMode( QListView::ListMode );
125  lstValues->setViewMode( QListView::ListMode );
126  lstFields->setSelectionBehavior( QAbstractItemView::SelectRows );
127  lstValues->setSelectionBehavior( QAbstractItemView::SelectRows );
128  // Performance tip since Qt 4.1
129  lstFields->setUniformItemSizes( true );
130  lstValues->setUniformItemSizes( true );
131  // Colored rows
132  lstFields->setAlternatingRowColors( true );
133  lstValues->setAlternatingRowColors( true );
134  lstValues->setModel( mProxyValues );
135 }
136 
137 void QgsQueryBuilder::fillValues( int idx, int limit )
138 {
139  // clear the model
140  mModelValues->clear();
141 
142  // determine the field type
143  QList<QVariant> values = mLayer->uniqueValues( idx, limit ).toList();
144  std::sort( values.begin(), values.end() );
145 
146  QString nullValue = QgsApplication::nullRepresentation();
147 
148  QgsDebugMsg( QStringLiteral( "nullValue: %1" ).arg( nullValue ) );
149 
150  const auto constValues = values;
151  for ( const QVariant &var : constValues )
152  {
153  QString value;
154  if ( var.isNull() )
155  value = nullValue;
156  else if ( var.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
157  value = var.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) );
158  else
159  value = var.toString();
160 
161  QStandardItem *myItem = new QStandardItem( value );
162  myItem->setEditable( false );
163  myItem->setData( var, Qt::UserRole + 1 );
164  mModelValues->insertRow( mModelValues->rowCount(), myItem );
165  QgsDebugMsg( QStringLiteral( "Value is null: %1\nvalue: %2" ).arg( var.isNull() ).arg( var.isNull() ? nullValue : var.toString() ) );
166  }
167 }
168 
169 void QgsQueryBuilder::btnSampleValues_clicked()
170 {
171  lstValues->setCursor( Qt::WaitCursor );
172 
173  QString prevSubsetString = mLayer->subsetString();
174  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
175  {
176  mLayer->setSubsetString( QString() );
177  }
178 
179  //Clear and fill the mModelValues
180  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), 25 );
181 
182  if ( prevSubsetString != mLayer->subsetString() )
183  {
184  mLayer->setSubsetString( prevSubsetString );
185  }
186 
187  lstValues->setCursor( Qt::ArrowCursor );
188 }
189 
190 void QgsQueryBuilder::btnGetAllValues_clicked()
191 {
192  lstValues->setCursor( Qt::WaitCursor );
193 
194  QString prevSubsetString = mLayer->subsetString();
195  if ( mUseUnfilteredLayer->isChecked() && !prevSubsetString.isEmpty() )
196  {
197  mLayer->setSubsetString( QString() );
198  }
199 
200  //Clear and fill the mModelValues
201  fillValues( mModelFields->data( lstFields->currentIndex(), Qt::UserRole + 1 ).toInt(), -1 );
202 
203  if ( prevSubsetString != mLayer->subsetString() )
204  {
205  mLayer->setSubsetString( prevSubsetString );
206  }
207 
208  lstValues->setCursor( Qt::ArrowCursor );
209 }
210 
212 {
213  // test the sql statement to see if it works
214  // by counting the number of records that would be
215  // returned
216 
217  if ( mLayer->setSubsetString( txtSQL->text() ) )
218  {
219  mUseUnfilteredLayer->setDisabled( mLayer->subsetString().isEmpty() );
220 
221  const long featureCount { mLayer->featureCount() };
222  // Check for errors
223  if ( featureCount < 0 )
224  {
225  QMessageBox::warning( this,
226  tr( "Query Result" ),
227  tr( "An error occurred when executing the query, please check the expression syntax." ) );
228  }
229  else
230  {
231  QMessageBox::information( this,
232  tr( "Query Result" ),
233  tr( "The where clause returned %n row(s).", "returned test rows", featureCount ) );
234  }
235  }
236  else if ( mLayer->dataProvider()->hasErrors() )
237  {
238  QMessageBox::warning( this,
239  tr( "Query Result" ),
240  tr( "An error occurred when executing the query." )
241  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
242  mLayer->dataProvider()->clearErrors();
243  }
244  else
245  {
246  QMessageBox::warning( this,
247  tr( "Query Result" ),
248  tr( "An error occurred when executing the query." ) );
249  }
250 }
251 
253 {
254  if ( txtSQL->text() != mOrigSubsetString )
255  {
256  if ( !mLayer->setSubsetString( txtSQL->text() ) )
257  {
258  //error in query - show the problem
259  if ( mLayer->dataProvider()->hasErrors() )
260  {
261  QMessageBox::warning( this,
262  tr( "Query Result" ),
263  tr( "An error occurred when executing the query." )
264  + tr( "\nThe data provider said:\n%1" ).arg( mLayer->dataProvider()->errors().join( QStringLiteral( "\n" ) ) ) );
265  mLayer->dataProvider()->clearErrors();
266  }
267  else
268  {
269  QMessageBox::warning( this, tr( "Query Result" ), tr( "Error in query. The subset string could not be set." ) );
270  }
271 
272  return;
273  }
274  }
275 
276  QDialog::accept();
277 }
278 
280 {
281  if ( mLayer->subsetString() != mOrigSubsetString )
282  mLayer->setSubsetString( mOrigSubsetString );
283 
284  QDialog::reject();
285 }
286 
287 void QgsQueryBuilder::btnEqual_clicked()
288 {
289  txtSQL->insertText( QStringLiteral( " = " ) );
290  txtSQL->setFocus();
291 }
292 
293 void QgsQueryBuilder::btnLessThan_clicked()
294 {
295  txtSQL->insertText( QStringLiteral( " < " ) );
296  txtSQL->setFocus();
297 }
298 
299 void QgsQueryBuilder::btnGreaterThan_clicked()
300 {
301  txtSQL->insertText( QStringLiteral( " > " ) );
302  txtSQL->setFocus();
303 }
304 
305 void QgsQueryBuilder::btnPct_clicked()
306 {
307  txtSQL->insertText( QStringLiteral( "%" ) );
308  txtSQL->setFocus();
309 }
310 
311 void QgsQueryBuilder::btnIn_clicked()
312 {
313  txtSQL->insertText( QStringLiteral( " IN " ) );
314  txtSQL->setFocus();
315 }
316 
317 void QgsQueryBuilder::btnNotIn_clicked()
318 {
319  txtSQL->insertText( QStringLiteral( " NOT IN " ) );
320  txtSQL->setFocus();
321 }
322 
323 void QgsQueryBuilder::btnLike_clicked()
324 {
325  txtSQL->insertText( QStringLiteral( " LIKE " ) );
326  txtSQL->setFocus();
327 }
328 
330 {
331  return txtSQL->text();
332 }
333 
334 void QgsQueryBuilder::setSql( const QString &sqlStatement )
335 {
336  txtSQL->setText( sqlStatement );
337 }
338 
339 void QgsQueryBuilder::lstFields_clicked( const QModelIndex &index )
340 {
341  if ( mPreviousFieldRow != index.row() )
342  {
343  mPreviousFieldRow = index.row();
344 
345  btnSampleValues->setEnabled( true );
346  btnGetAllValues->setEnabled( true );
347 
348  mModelValues->clear();
349  mFilterLineEdit->clear();
350  }
351 }
352 
353 void QgsQueryBuilder::lstFields_doubleClicked( const QModelIndex &index )
354 {
355  txtSQL->insertText( '\"' + mLayer->fields().at( mModelFields->data( index, Qt::UserRole + 1 ).toInt() ).name() + '\"' );
356  txtSQL->setFocus();
357 }
358 
359 void QgsQueryBuilder::lstValues_doubleClicked( const QModelIndex &index )
360 {
361  QVariant value = index.data( Qt::UserRole + 1 );
362  if ( value.isNull() )
363  txtSQL->insertText( QStringLiteral( "NULL" ) );
364  else if ( value.type() == QVariant::Date && mLayer->providerType() == QLatin1String( "ogr" ) && mLayer->storageType() == QLatin1String( "ESRI Shapefile" ) )
365  txtSQL->insertText( '\'' + value.toDate().toString( QStringLiteral( "yyyy/MM/dd" ) ) + '\'' );
366  else if ( value.type() == QVariant::Int || value.type() == QVariant::Double || value.type() == QVariant::LongLong || value.type() == QVariant::Bool )
367  txtSQL->insertText( value.toString() );
368  else
369  txtSQL->insertText( '\'' + value.toString().replace( '\'', QLatin1String( "''" ) ) + '\'' );
370 
371  txtSQL->setFocus();
372 }
373 
374 void QgsQueryBuilder::btnLessEqual_clicked()
375 {
376  txtSQL->insertText( QStringLiteral( " <= " ) );
377  txtSQL->setFocus();
378 }
379 
380 void QgsQueryBuilder::btnGreaterEqual_clicked()
381 {
382  txtSQL->insertText( QStringLiteral( " >= " ) );
383  txtSQL->setFocus();
384 }
385 
386 void QgsQueryBuilder::btnNotEqual_clicked()
387 {
388  txtSQL->insertText( QStringLiteral( " != " ) );
389  txtSQL->setFocus();
390 }
391 
392 void QgsQueryBuilder::btnAnd_clicked()
393 {
394  txtSQL->insertText( QStringLiteral( " AND " ) );
395  txtSQL->setFocus();
396 }
397 
398 void QgsQueryBuilder::btnNot_clicked()
399 {
400  txtSQL->insertText( QStringLiteral( " NOT " ) );
401  txtSQL->setFocus();
402 }
403 
404 void QgsQueryBuilder::btnOr_clicked()
405 {
406  txtSQL->insertText( QStringLiteral( " OR " ) );
407  txtSQL->setFocus();
408 }
409 
410 void QgsQueryBuilder::onTextChanged( const QString &text )
411 {
412  mProxyValues->setFilterCaseSensitivity( Qt::CaseInsensitive );
413  mProxyValues->setFilterWildcard( text );
414 }
415 
417 {
418  txtSQL->clear();
419  mLayer->setSubsetString( QString() );
420  mUseUnfilteredLayer->setDisabled( true );
421 }
422 
423 void QgsQueryBuilder::btnILike_clicked()
424 {
425  txtSQL->insertText( QStringLiteral( " ILIKE " ) );
426  txtSQL->setFocus();
427 }
428 
430 {
431  lblDataUri->setText( uri );
432 }
433 
434 void QgsQueryBuilder::showHelp()
435 {
436  QgsHelp::openHelp( QStringLiteral( "working_with_vector/vector_properties.html#query-builder" ) );
437 }
QgsFields::OriginProvider
@ OriginProvider
Field comes from the underlying data provider of the vector layer (originIndex = index in provider's ...
Definition: qgsfields.h:49
QgsVectorDataProvider::hasErrors
bool hasErrors() const
Provider has errors to report.
Definition: qgsvectordataprovider.cpp:675
QgsVectorLayer::dataProvider
QgsVectorDataProvider * dataProvider() FINAL
Returns the layer's data provider, it may be nullptr.
Definition: qgsvectorlayer.cpp:610
qgsgui.h
QgsField::displayNameWithAlias
QString displayNameWithAlias() const
Returns the name to use when displaying this field and adds the alias in parenthesis if it is defined...
Definition: qgsfield.cpp:97
QgsFields::count
int count() const
Returns number of items.
Definition: qgsfields.cpp:133
QgsFields
Definition: qgsfields.h:42
QgsDebugMsg
#define QgsDebugMsg(str)
Definition: qgslogger.h:38
QgsVectorLayer::featureCount
long featureCount(const QString &legendKey) const
Number of features rendered with specified legend key.
Definition: qgsvectorlayer.cpp:729
QgsQueryBuilder::accept
void accept() override
Definition: qgsquerybuilder.cpp:252
QgsField::name
QString name
Definition: qgsfield.h:59
QgsQueryBuilder::showEvent
void showEvent(QShowEvent *event) override
Definition: qgsquerybuilder.cpp:85
qgsquerybuilder.h
QgsMapLayer::providerType
QString providerType() const
Returns the provider type (provider key) for this layer.
Definition: qgsmaplayer.cpp:1608
qgsapplication.h
QgsGui::enableAutoGeometryRestore
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...
Definition: qgsgui.cpp:133
QgsVectorLayer::fields
QgsFields fields() const FINAL
Returns the list of fields of this layer.
Definition: qgsvectorlayer.cpp:3203
QgsVectorDataProvider::errors
QStringList errors() const
Gets recorded errors.
Definition: qgsvectordataprovider.cpp:680
QgsApplication::nullRepresentation
static QString nullRepresentation()
This string is used to represent the value NULL throughout QGIS.
Definition: qgsapplication.cpp:1786
QgsVectorLayer::uniqueValues
QSet< QVariant > uniqueValues(int fieldIndex, int limit=-1) const FINAL
Calculates a list of unique values contained within an attribute in the layer.
Definition: qgsvectorlayer.cpp:3872
QgsQueryBuilder::reject
void reject() override
Definition: qgsquerybuilder.cpp:279
QgsFields::fieldOrigin
FieldOrigin fieldOrigin(int fieldIdx) const
Gets field's origin (value from an enumeration)
Definition: qgsfields.cpp:189
qgsvectordataprovider.h
QgsQueryBuilder::QgsQueryBuilder
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.
Definition: qgsquerybuilder.cpp:32
QgsVectorDataProvider::clearErrors
void clearErrors()
Clear recorded errors.
Definition: qgsvectordataprovider.cpp:670
qgsvectorlayer.h
QgsQueryBuilder::test
void test()
Test the constructed sql statement to see if the vector layer data provider likes it.
Definition: qgsquerybuilder.cpp:211
QgsGui::instance
static QgsGui * instance()
Returns a pointer to the singleton instance.
Definition: qgsgui.cpp:62
QgsHelp::openHelp
static void openHelp(const QString &key)
Opens help topic for the given help key using default system web browser.
Definition: qgshelp.cpp:36
QgsVectorLayer
Definition: qgsvectorlayer.h:386
qgssettings.h
QgsMapLayer::name
QString name
Definition: qgsmaplayer.h:84
QgsQueryBuilder::sql
QString sql()
Definition: qgsquerybuilder.cpp:329
QgsVectorLayer::storageType
QString storageType() const
Returns the permanent storage type for this layer as a friendly name.
Definition: qgsvectorlayer.cpp:323
QgsVectorLayer::subsetString
QString subsetString
Definition: qgsvectorlayer.h:390
QgsQueryBuilder::setDatasourceDescription
void setDatasourceDescription(const QString &uri)
Definition: qgsquerybuilder.cpp:429
qgslogger.h
QgsFields::at
QgsField at(int i) const
Gets field at particular index (must be in range 0..N-1)
Definition: qgsfields.cpp:163
QgsQueryBuilder::clear
void clear()
Definition: qgsquerybuilder.cpp:416
QgsQueryBuilder::setSql
void setSql(const QString &sqlStatement)
Definition: qgsquerybuilder.cpp:334
qgshelp.h
qgsproject.h
QgsVectorLayer::setSubsetString
virtual bool setSubsetString(const QString &subset)
Sets the string (typically sql) used to define a subset of the layer.
Definition: qgsvectorlayer.cpp:921